One thing that confused me when I started learning SQL was JOINs.
I understood tables. I understood SELECT, INSERT, UPDATE, and DELETE.
Then JOINs came in and suddenly I had to combine data from different tables.
Eventually, it clicked: JOINs are basically how we connect related tables.
A simple example
Imagine we have a customers table:
customer_id, customer_name
And an orders table:
order_id, customer_id, amount
Both tables have customer_id, so we can connect them.
INNER JOIN
This gives us only customers who actually have an order.
SELECT customers.customer_name, orders.amount FROM customers INNER JOIN orders ON customers.customer_id = orders.customer_id;
This is probably the JOIN I use most when I only care about matching records.
LEFT JOIN
A LEFT JOIN gives us every customer, even if they haven't placed an order.
SELECT customers.customer_name, orders.amount FROM customers LEFT JOIN orders ON customers.customer_id = orders.customer_id;
If a customer has no order, the order information will simply be NULL.
This is useful for questions like:
"Which customers have never placed an order?"
RIGHT JOIN
A RIGHT JOIN is basically the opposite of a LEFT JOIN.
SELECT customers.customer_name, orders.amount FROM customers RIGHT JOIN orders ON customers.customer_id = orders.customer_id;
It keeps everything from the right table.
Honestly, I don't use RIGHT JOIN as much because I can usually just switch the tables and use LEFT JOIN.
FULL OUTER JOIN
This one gives us everything from both tables, whether there is a match or not.
SELECT customers.customer_name, orders.amount FROM customers FULL OUTER JOIN orders ON customers.customer_id = orders.customer_id;
It can be useful when comparing two datasets and trying to find missing matches.
CROSS JOIN
This one is a little different.
It creates every possible combination between two tables.
For example, if we have:
Red, Blue
and:
Small, Medium
we get:
Red - Small
Red - Medium
Blue - Small
Blue - Medium
The query is:
SELECT colors.color, sizes.size FROM colors CROSS JOIN sizes;
It's useful in situations where you actually need all possible combinations.
The easiest way I remember JOINs
INNER JOIN → matching records
LEFT JOIN → everything on the left
RIGHT JOIN → everything on the right
FULL OUTER JOIN → everything from both
CROSS JOIN → every possible combination
Final thought
JOINs looked complicated to me at first, but once I stopped trying to memorize the definitions and started thinking about which records I actually wanted, they became much easier.
That's probably the biggest thing I'm learning with SQL: understanding the problem first makes the query much easier to write.
Top comments (0)