Must-Know SQL Differences: Rank vs Dense_Rank, Join vs Union & More

Imagine you’re managing a large bookstore’s database. You need to rank the best-selling books, filter by genre, merge customer orders, and remove outdated inventory. The tools for these tasks are SQL commands and concepts—but do you know the right ones to use? SQL (Structured Query Language) is the foundation of relational databases, and understanding its nuances can save time and improve performance. Lets demystify the essential sql differences between commonly used SQL concepts like RANK vs DENSE_RANK, HAVING vs WHERE, UNION vs UNION ALL, and more. Let’s dive in with practical examples and scenarios!

RANK vs DENSE_RANK

Consider a bookstore wanting to rank books by sales. Both RANK and DENSE_RANK assign ranks, but they handle ties differently.

Code Example:

-- Sample book sales data
CREATE TABLE BookSales ( 
    BookName VARCHAR(50), 
    Sales INT 
);

INSERT INTO BookSales (BookName, Sales) 
VALUES ('Book A', 100), ('Book B', 80), ('Book C', 80), ('Book D', 60);

-- Using RANK
SELECT BookName, Sales, RANK() OVER (ORDER BY Sales DESC) AS Rank
FROM BookSales;

-- Using DENSE_RANK
SELECT BookName, Sales, DENSE_RANK() OVER (ORDER BY Sales DESC) AS Rank
FROM BookSales;

Output:

Explanation:

• RANK: Skips ranks after ties (e.g., two “2” ranks, then jumps to “4”).
• DENSE_RANK: No skipped ranks (ties still count as one).

Real-life scenario: Use RANK for sports competitions (to reflect skipped positions) and DENSE_RANK for customer loyalty programs (consistent tier levels).

HAVING vs WHERE Clause

Imagine filtering books by genre and then filtering further for best-sellers.

• HAVING filters rows after aggregation.
• WHERE filters rows before aggregation.

Code Example:

-- Filter books with genre Fiction and sales above 100
SELECT Genre, COUNT(*) AS TotalBooks
FROM BookSales
WHERE Genre = 'Fiction' -- Applied before aggregation
GROUP BY Genre
HAVING COUNT(*) > 10; -- Applied after aggregation

Scenario: Use WHERE for individual row conditions and HAVING for summary conditions.

Table Comparison:

UNION vs UNION ALL

Combining orders from two warehouse databases:

• UNION: Removes duplicates.
• UNION ALL: Keeps duplicates.

Code Example:

-- Orders from two warehouses
SELECT CustomerID, OrderID FROM Warehouse1
UNION ALL
SELECT CustomerID, OrderID FROM Warehouse2;

-- To remove duplicates
SELECT CustomerID, OrderID FROM Warehouse1
UNION
SELECT CustomerID, OrderID FROM Warehouse2;

Output:

Performance tip: Use UNION ALL when duplicates are acceptable for faster results.

JOIN vs UNION

If customers order multiple books, how do you get a combined view?

• JOIN: Combines columns from multiple tables.
• UNION: Combines rows from multiple queries.

JOIN Example:

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

UNION Example:

SELECT CustomerName FROM Customers
UNION ALL
SELECT CustomerName FROM Leads;

DELETE vs DROP vs TRUNCATE

How do you remove data efficiently?

• TRUNCATE: Removes all rows but retains structure.
• DELETE: Removes rows with conditions.
• DROP: Deletes entire tables.

Code Comparison:

DELETE FROM Books WHERE Sales < 50; -- Remove specific rows
DROP TABLE Books; -- Delete table
TRUNCATE TABLE Books; -- Clear table but keep structure

Tip: Use TRUNCATE for performance when clearing large datasets.

CTE vs Temporary Tables

Common Table Expressions (CTEs) and Temporary Tables are used to store temporary result sets. However, they differ in scope and usage.

• CTE: Declared using the WITH keyword, scoped to a single query.
• Temporary Table: Created with CREATE TABLE #TempTable, persists until the session ends.

CTE Example:

WITH SalesRank AS (
    SELECT BookName, RANK() OVER (ORDER BY Sales DESC) AS Rank
    FROM BookSales
)
SELECT * FROM SalesRank WHERE Rank <= 3;

Temporary Table Example:

CREATE TABLE #TempSalesRank (BookName VARCHAR(50), Rank INT);

INSERT INTO #TempSalesRank
SELECT BookName, RANK() OVER (ORDER BY Sales DESC) AS Rank
FROM BookSales;

SELECT * FROM #TempSalesRank WHERE Rank <= 3;

Comparison:

Scenario: Use CTEs for readability and Temporary Tables for more complex data manipulations.

Subqueries vs CTEs

Both subqueries and CTEs help break down complex queries, but they have different readability and reuse benefits.

Subquery Example:

SELECT * FROM BookSales
WHERE Sales = (SELECT MAX(Sales) FROM BookSales);

CTE Example:

WITH MaxSales AS (
    SELECT MAX(Sales) AS MaxSales FROM BookSales
)
SELECT * FROM BookSales WHERE Sales = (SELECT MaxSales FROM MaxSales);

Scenario: CTEs are preferred for readability, especially for complex queries.

ISNULL vs COALESCE

Both handle NULL values, but COALESCE is more versatile.

Code Example:

SELECT ISNULL(Sales, 0) AS SalesValue FROM BookSales;
SELECT COALESCE(Sales, 0) AS SalesValue FROM BookSales;

Difference:

• ISNULL: Only takes two arguments.
• COALESCE: Can take multiple arguments and returns the first non-NULL value.

INTERSECT vs INNER JOIN

• INTERSECT: Returns common rows between two result sets.
• INNER JOIN: Combines data based on matching keys.

Code Example:

-- INTERSECT Example
SELECT CustomerID FROM Orders1
INTERSECT
SELECT CustomerID FROM Orders2;

-- INNER JOIN Example
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

EXCEPT vs NOT IN

• EXCEPT: Returns rows from one query not present in another.
• NOT IN: Filters rows not matching a list of values.

Code Example:

-- EXCEPT Example
SELECT CustomerID FROM Orders1
EXCEPT
SELECT CustomerID FROM Orders2;

-- NOT IN Example
SELECT * FROM BookSales
WHERE Genre NOT IN ('Fiction', 'Non-Fiction');

Wrap UP

Understanding these differences is crucial for writing efficient, accurate SQL queries and managing data effectively. Whether you’re optimizing performance, ensuring data integrity, or simplifying query logic, knowing when and how to use each SQL command can make all the difference. By mastering these distinctions, you’ll take your SQL skills to the next level.

FAQs