Joining tables · solulu

This lesson covers JOIN … ON, table aliases, LEFT JOIN, IS NULL, LIKE, COUNT(col) vs COUNT(*). Read it, then solve questions 21–30 below it in the sidebar. Every query runs on a real PostgreSQL database inside your browser.

Why join

sales only stores ids (product_id, customer_id). To see names, categories or prices you join the table that holds them. The ON condition says which rows belong together: usually a foreign key equal to a primary key.

SELECT s.sale_id, c.customer_name, s.total_price
FROM sales s
JOIN customers c ON s.customer_id = c.customer_id
WHERE c.city = 'Mumbai';

Aliases

FROM sales s lets you write s.total_price instead of sales.total_price. When two joined tables have a column with the same name (like product_id), you must say which one you mean: s.product_id or p.product_id.

Inner join vs left join

JOIN (inner) keeps only pairs that match. A product with no sales disappears.
LEFT JOIN keeps every row of the left table; where nothing matches, the right table’s columns are NULL.

To find rows with no match, left join and keep WHERE right_table.key IS NULL. Never write = NULL: any comparison with NULL is unknown, so it matches nothing.

SELECT c.customer_name, s.sale_id
FROM customers c
LEFT JOIN sales s ON s.customer_id = c.customer_id
ORDER BY s.sale_id NULLS FIRST
LIMIT 5;

Counting after a left join

COUNT(*) counts rows, so an unmatched left row still counts as 1. COUNT(s.sale_id) counts non-NULL values, so it gives 0 for no matches. Group by the left table’s key.

SELECT c.customer_name, COUNT(s.sale_id) AS bills, COUNT(*) AS rows_after_join
FROM customers c
LEFT JOIN sales s ON s.customer_id = c.customer_id
GROUP BY c.customer_id, c.customer_name;

Pattern matching with LIKE

LIKE matches text against a pattern: % = any run of characters, _ = exactly one character. 'Head%' starts with Head, '%Pack' ends with Pack. PostgreSQL’s ILIKE ignores case.

SELECT product_name FROM products WHERE product_name LIKE '%e_';

Joining three tables

Chain joins: each JOIN … ON adds a table. sales is the bridge between customers and products.

SELECT c.customer_name, p.category, s.quantity_sold
FROM sales s
JOIN customers c ON c.customer_id = s.customer_id
JOIN products p ON p.product_id = s.product_id
WHERE p.category = 'Snacks';