Sql query fetching data from multiple tables

sql

join

subqueries

performance

byNikita Barsukov·Sep 3, 2024

Fetch data from different tables using SQL JOINs. An INNER JOIN returns records with matching values in both tables:

SELECT a.name, b.price
FROM products AS a
INNER JOIN sales AS b ON a.id = b.product_id;

This collects product names and their corresponding sales prices, in cases where the product IDs match in both tables.

Comprehending JOINs and table aliasing

In SQL, the JOIN clause combines rows from two or more tables based on a related column. Understanding how to use different types of JOINs is key to mastering data retrieval from multiple tables:

INNER JOIN: Returns records with matching values in both tables.
LEFT JOIN (or LEFT OUTER JOIN): Returns all records from the left table, and any matched records from the right.
RIGHT JOIN (or RIGHT OUTER JOIN): Returns all records from the right table, and any matched records from the left.
FULL JOIN (or FULL OUTER JOIN): Returns all records when there is a match in either left (or right) table.

Utilizing table aliases (AS) can make the query more readable, and helps prevent issues caused by ambiguous column names:

SELECT p.product_name, s.sale_date
FROM products p
INNER JOIN sales s ON p.id = s.product_id;

Here, p and s are aliases for products and sales tables respectively.

Applying set operators and subqueries for complex data

Set operators like UNION and INTERSECT, along with subqueries can be used to achieve complex filtering.

UNION: Combines rows from two or more select statements and removes duplicate rows.
UNION ALL: Similar to UNION but does not remove duplicate rows.

Subqueries, or nested queries can be used in various contexts and add another dimension to our SQL querying capabilities:

SELECT name
FROM products
WHERE id IN (SELECT product_id FROM sales);

In this case, the subquery fetches product_ids from sales table to filter products.

SQL syntax and performance oddities

When joining multiple tables, remember to account for NULL values.

SELECT a.name, b.price
FROM products AS a
LEFT JOIN sales AS b ON a.id = b.product_id; --returns NULL for unsold products

To avoid fetching duplicate rows and enhance performance, DISTINCT can be used in the select clause.

Grouping and aggregation of complex datasets

Working with aggregate functions like SUM, AVG, MIN, MAXor COUNT, can provide valuable insights from your data.

SELECT a.category, COUNT(*)
FROM products a
JOIN sales b ON a.id = b.product_id
GROUP BY a.category;

Oh look, we now know how many items were sold in each product category.

The grand temporal showdown: Using LEAST and GREATEST

In a world where time is of essence, the LEAST and GREATEST functions can come in handy to quickly identify the earliest or latest records.

SELECT GREATEST(a.date_modified, b.date_modified) AS last_update --The ultimate time lord showdown
FROM table_a a
JOIN table_b b ON a.id = b.foreign_id;