Introduction
Subqueries, CTEs, UNION, and CASE statements are all advanced SQL concepts that can be used to write more complex and efficient queries.
Subqueries
A subquery is a query that is nested within another query. Subqueries can be used to filter data, perform calculations, and aggregate data.
For example, the following query uses a subquery to select all customers who have placed an order in the last month:
SELECT * FROM customers
WHERE customer_id IN (
SELECT customer_id FROM orders
WHERE order_date >= '2023-10-13'
);The subquery in this example selects all customers who have placed an order in the last month. The outer query then selects all customers whose customer_id is in the list of customer_ids returned by the subquery.
Common Table Expressions (CTEs)
A common table expression (CTE) is a temporary named subquery. CTEs can be used to simplify complex queries and make them more readable.
For example, the following query uses a CTE to calculate the total sales for each product category:
WITH product_categories AS (
SELECT category_name
FROM products
GROUP BY category_name
)
SELECT category_name, SUM(sales) AS total_sales
FROM products
JOIN product_categories ON products.category_id = product_categories.category_id
GROUP BY category_name;The CTE in this example names the subquery that calculates the product categories. The outer query then joins the products table with the CTE to calculate the total sales for each product category.
UNION
The UNION operator is used to combine the results of two or more queries into a single result set. The UNION operator can be used to combine the results of queries of different shapes and sizes.
For example, the following query uses the UNION operator to combine the results of two queries that select customers from different countries:
SELECT * FROM customers
WHERE country = 'USA'
UNION
SELECT * FROM customers
WHERE country = 'Canada';This query will return a single result set that contains all customers from the USA and Canada.
CASE Statements
The CASE statement is a conditional statement that can be used to evaluate different conditions and return different values depending on the outcome.
For example, the following query uses a CASE statement to calculate the shipping cost for an order based on the order total:
SELECT order_total,
CASE
WHEN order_total < 100 THEN 10
WHEN order_total >= 100 AND order_total < 200 THEN 15
ELSE 20
END AS shipping_cost
FROM orders;This query will calculate the shipping cost for each order based on the order total. Orders with a total of less than $100 will have a shipping cost of $10. Orders with a total of $100 or more but less than $200 will have a shipping cost of $15. Orders with a total of $200 or more will have a shipping cost of $20.
Conclusion
Subqueries, CTEs, UNION, and CASE statements are all powerful tools that can be used to write more complex and efficient SQL queries. By understanding these advanced SQL concepts, you can get more out of your data and solve more complex business problems.
