A Secret Method for Joining Large Tables in SQL
Here's What Your SQL Tutorial Channel Doesn't Tell You About Joins
What are the joins?
A join combines rows from two or more tables using a related column. The result is a single table that contains columns from both source tables.
This process is similar to how you use VLOOKUP, XLOOKUP, and INDEX+MATCH in Excel.
Use cases:
Data enrichment.
Use JOIN as a filtering mechanism. You can verify whether a record exists in another table without pulling columns from that table into the final output.
Basic joins:
Inner join:
An inner join returns only rows that have matching values in both tables.
The order of the tables does not matter for an inner join. A table swap will create the same result because both tables have equal priority.
It is the default join type in SQL. Use JOIN or INNER JOIN commands to do the inner join.
Some SQL dialects do not support FULL OUTER JOIN directly and should use workarounds.
Example:
SELECT
trs.transaction_id,
trs.transaction_date,
trs.amount,
cust.customer_id,
cust.customer_name,
cust.city
FROM
transactions trs
JOIN
customers cust
ON trs.customer_id = cust.customer_id;Left join:
A left join returns every row from the left table and only the matching rows from the right table. When there are no matches, SQL will fill the right-table columns with null values.
The order of tables here is critical.
Example:
SELECT
cust.customer_id,
cust.customer_name,
cust.city,
ord.order_id,
ord.order_date,
ord.total_amount
FROM
customers cust
LEFT JOIN
orders ord
ON cust.customer_id = ord.customer_id;Right join:
It works the same way as a left join.
It returns every row from the right table and only the matching rows from the left table. In case there is no match, left table columns will return null.
Example:
SELECT
cust.customer_id,
cust.customer_name,
cust.city,
ord.order_id,
ord.order_date,
ord.total_amount
FROM
customers cust
RIGHT JOIN
orders ord
ON cust.customer_id = ord.customer_id;Cross join:
A cross join creates a Cartesian product where every row from the first table is paired with every row from the second table.
Example:
SELECT
emp.employee_id,
emp.employee_name,
emp.department,
proj.project_id,
proj.project_name,
proj.start_date
FROM
employees emp
CROSS JOIN
projects proj;Outer join / full outer join:
It returns every row from both tables.
Unmatched rows on either side will get NULLs from the other.
Example:
SELECT
pass.name,
tick.embarked_port
FROM
passengers pass
FULL OUTER JOIN
tickets tick ON tick.passenger_id = pass.id;Keys and Complex Join Conditions:
SQL performs joins using the key columns.
There are two types of keys:
Primary key. A primary key is a column, or a set of columns, that identifies each row in a table with unique value. Example: ID column.
Foreign key. A foreign key is a column in one table that references the primary key of another table.
Align the data types and values before the join.
Complex Join Conditions:
You can place filter conditions either in the WHERE clause or inside the ON clause of a join.
Both approaches create the same result for inner joins, but they work differently with left joins.
A condition in the ON clause of a left join filters the right table before the join.
A condition in the WHERE clause filters the result after the join and can remove left-table rows.
Examples:
Condition in the join: it preserves all products and shows only matching shipments.
SELECT
prod.product_id,
prod.product_name,
prod.category,
prod.price,
shp.shipment_id,
shp.date,
shp.quantity,
shp.destination
FROM
products prod
LEFT JOIN
shipments shp
ON prod.product_id = shp.product_id
AND shp.date = ‘2026-02-01’;Condition in WHERE: it filters after the join and removes products without shipments on that date:
SELECT
prod.product_id,
prod.product_name,
prod.category,
prod.price,
shp.shipment_id,
shp.date,
shp.quantity,
shp.destination
FROM
products prod
LEFT JOIN
shipments shp
ON prod.product_id = shp.product_id
WHERE
shp.date = ‘2026-02-01’;Advanced joins:
Self-join:
A self-join joins a table to itself.
You can use self-joins for tables with hierarchical or sequential relationships.
Each instance of the table in the query should have its own alias.
Example:
SELECT
e.name AS employee,
m.name AS manager
FROM
employees emp
JOIN
employees mgr
ON emp.manager_id = mgr.id;Lateral join:
LATERAL keyword makes a subquery in the FROM clause reference columns from preceding tables.
Without LATERAL, a subquery in FROM is evaluated independently.
SQL Server example:
SELECT
ords.order_id,
itmTop.product_name
FROM
Orders ords
CROSS APPLY (
SELECT
TOP (1) oi.product_name
FROM
OrderItems oitm
WHERE
oitm.order_id = ords.order_id
ORDER BY
oitm.quantity DESC
) itmTop;Non-Equi (Theta) Join:
Standard joins use “=” as the join condition. Non-equi joins use <, >, <=, >=, BETWEEN, or <>.
Use them for range-based lookups.
Example: Salary bands join.
SELECT
emp.name,
emp.salary,
salbnd.band_name
FROM
employees emp
JOIN
salary_bands salbnd
ON emp.salary BETWEEN salbnd.min_salary
AND salbnd.max_salary;Joins with CTEs:
Complex joins can be organized into CTEs (Common Table Expressions).
A CTE stores an intermediate result set that subsequent queries can reference.
Example:
WITH feb_shipments AS (
SELECT p.product_name, s.amount, s.date
FROM products p
LEFT JOIN shipments s
ON p.product_id = s.product_id
AND s.date = ‘2022-02-01’
)
SELECT
product_name,
CASE WHEN COALESCE(SUM(amount),0) > 0
THEN ‘Shipped’
ELSE ‘Not Shipped’
END AS status
FROM
feb_shipments
GROUP BY
product_name;Anti-join:
An anti-join finds rows in one table that have no match in another table.
The technique uses a left join followed by a WHERE clause that checks for null values in the right table’s key column.
Example:
SELECT
prod.product_id,
prod.product_name
FROM
products prod
LEFT JOIN
shipments shp
ON prod.product_id = shp.product_id
WHERE
shp.product_id IS NULL;Semi-join:
A semi-join returns rows from the left table that have at least one match in the right table, without duplicating left-table rows when multiple matches exist.
Example: Semi join via EXISTS:
SELECT
cust.customer_id,
cust.customer_name
FROM
customers cust
WHERE EXISTS (
SELECT
1
FROM
orders ord
WHERE
ord.customer_id = cust.customer_id
AND ord.amount > 100
);How to improve the join performance?
Non-equi joins are often slower than equi-joins because many optimizers cannot use hash joins or some index-based strategies that rely on equality. The performance depends on the engine and data, so verify it with EXPLAIN/EXPLAIN ANALYZE.
Index Join Columns. Indexes on join columns improve query speed. A join without indexes will force the optimizer to scan entire tables.
Filter Before Joining. Apply WHERE conditions to reduce the dataset size before joining when possible.
Best Practices:
Select specific columns instead of using SELECT *.
Use COALESCE or explicit NULL checks for NULLs in the join column.
Keep join conditions simple. When you need a filter, move conditions to the WHERE clause for inner joins or to a CTE for outer joins.
Specify the join type explicitly.
Use aliases when you join tables, but do not make them super short: a single or two letters. They should be descriptive, because after some time you will not be able to recognize what means what.
Subscribe for more SQL deep dives.
What’s one join concept you still find tricky?
Let me know in the comments. 👇