SQL Essentials: ORDER BY, LIMIT, OFFSET, UNION, INTERSECT, and More

1. ORDER BY — Sorting Results

Used to sort query output in ascending (ASC, default) or descending (DESC) order.

-- Sort products by price ascending
SELECT * FROM products
ORDER BY price;

-- Sort by price descending
SELECT * FROM products
ORDER BY price DESC;

✅ You can sort by multiple columns too:

-- First by category, then by price descending
SELECT * FROM products
ORDER BY category, price DESC;

---

🔢 2. LIMIT — Restrict Number of Rows

Returns only a limited number of records.

-- Show top 5 products
SELECT * FROM products
ORDER BY price DESC
LIMIT 5;

---

🔁 3. OFFSET — Skip Rows

Used with LIMIT to paginate results.

-- Skip first 5, get next 5 (for page 2)
SELECT * FROM products
ORDER BY price DESC
LIMIT 5 OFFSET 5;

📌 Equivalent to: "Show me page 2 of top results (5 per page)."

---

🎯 4. DISTINCT — Remove Duplicate Rows

Removes duplicate records in the output.

-- Unique categories
SELECT DISTINCT category FROM products;

-- Unique combination of category and brand
SELECT DISTINCT category, brand FROM products;

---

🔗 5. UNION — Combine Results Vertically (Remove Duplicates)

Combines results of two queries with distinct results.

( 
SELECT name FROM employees
)
UNION
(
SELECT name FROM customers;
);

✅ Ensures no duplicates.

---

🔗 6. UNION ALL — Combine Results with Duplicates

Same as UNION but includes duplicates.

(
SELECT name FROM employees
)
UNION ALL
(
SELECT name FROM customers;
);

---

🔍 7. INTERSECT — Common Records

Returns rows common to both queries.

(
SELECT name FROM employees
)
INTERSECT
(
SELECT name FROM customers;
);

PostgreSQL supports INTERSECT, but some DBs like MySQL do not.

---

🚫 8. EXCEPT — Remove Matching Records

Returns rows from the first query that are not in the second.

(
SELECT name FROM employees
)
EXCEPT
(
SELECT name FROM customers;
);

Gives all employee names who are not customers.

---

🧠 Summary Table