Intermediate SQL Queries
Welcome to the intermediate SQL queries tutorial. This section will dive into more complex and powerful SQL concepts that are essential for efficient data manipulation and retrieval.
1. JOIN Operations
Learn how to combine rows from two or more tables based on a related column between them.
INNER JOIN
Returns only the rows where the join condition is met in both tables.
Example:
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;
LEFT JOIN (or LEFT OUTER JOIN)
Returns all rows from the left table, and the matched rows from the right table. If no match is found, NULL values are returned for the right table's columns.
Example:
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;
RIGHT JOIN (or RIGHT OUTER JOIN)
Returns all rows from the right table, and the matched rows from the left table. If no match is found, NULL values are returned for the left table's columns.
Example:
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
RIGHT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;
FULL JOIN (or FULL OUTER JOIN)
Returns all rows when there is a match in either the left or the right table. Returns NULL values where there is no match.
Example:
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;
2. Aggregate Functions and GROUP BY
Aggregate functions perform a calculation on a set of values and return a single value. The GROUP BY clause is used to group rows that have the same values in specified columns into summary rows, like "find the number of customers in each country".
Common aggregate functions include COUNT(), SUM(), AVG(), MIN(), and MAX().
Example: Counting customers per country
SELECT Country, COUNT(CustomerID) AS NumberOfCustomers
FROM Customers
GROUP BY Country
ORDER BY Country;
HAVING Clause
The HAVING clause is used to filter groups based on a specified condition. It is similar to the WHERE clause, but it is used for aggregate functions.
Example: Countries with more than 10 customers
SELECT Country, COUNT(CustomerID) AS NumberOfCustomers
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 10
ORDER BY Country;
3. Subqueries
A subquery (also known as a nested query or inner query) is a query within another SQL query. Subqueries can be used in the WHERE clause, FROM clause, or the SELECT clause.
Subqueries in WHERE Clause
Use subqueries to return a list of values to be compared.
Example: Find customers who have placed an order
SELECT CustomerName
FROM Customers
WHERE CustomerID IN (SELECT CustomerID FROM Orders);
Correlated Subqueries
A correlated subquery is a subquery that reads values from the outer query. It is executed once for each row processed by the outer query.
Example: Find customers whose order total is above the average order total for their country
SELECT c.CustomerName, o.OrderID, o.OrderTotal
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
WHERE o.OrderTotal > (
SELECT AVG(OrderTotal)
FROM Orders o2
WHERE o2.CustomerID = c.CustomerID
);
4. UNION Operator
The UNION operator is used to combine the result-set of two or more SELECT statements. By default, UNION selects only unique values. To return all values, including duplicates, use UNION ALL.
The SELECT statements by UNION must have:
- The same number of columns
- The columns must have similar data types
- The columns must be in the same order
Example: Combine customers from USA and Canada
SELECT CustomerName, City, Country FROM Customers WHERE Country='USA'
UNION
SELECT CustomerName, City, Country FROM Customers WHERE Country='Canada';
UNION ALL if you want to include duplicate rows in your result.
5. Window Functions
Window functions perform calculations across a set of table rows that are somehow related to the current row. This is similar to aggregate functions, but they do not cause rows to be grouped into a single output row. Instead, rows retain their separate identities.
Common window functions include ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), SUM() OVER(), AVG() OVER().
Example: Assign a row number to each customer within their country, ordered by name
SELECT
CustomerName,
Country,
ROW_NUMBER() OVER(PARTITION BY Country ORDER BY CustomerName) AS RowNumPerCountry
FROM Customers;
PARTITION BY clause divides the rows into partitions. The ORDER BY clause within the OVER clause specifies the order of rows within each partition.
By mastering these intermediate SQL concepts, you'll be well-equipped to handle more complex data analysis and database management tasks.