SQL Joins Explained
What SQL Joins Do in a Query
SQL joins are operations that combine rows from two or more tables based on a matching column, usually a key that links the tables together. A join lets one query return data that is stored in several places at once.
Relational databases split information into separate tables so the same facts are not repeated. A shop might keep customer names in a customers table and each purchase in an orders table, with every order storing the ID of the customer who placed it. A join matches those IDs, so one result can show a customer's name next to each of their orders.
The rule that decides which rows pair up is called the join condition. In most designs it compares a foreign key in one table with the primary key in the other.
Inner, Left, Right and Full Joins Compared
There are four common join types. They differ in what happens to rows that have no match in the other table.
- Inner join returns only the rows that match in both tables. A customer with no orders does not appear in the result.
- Left join returns every row from the left table, plus any matching rows from the right table. Where no match exists, the right-hand columns are empty, which databases show as
NULL. - Right join works the same way from the right table's point of view. It is the mirror image of a left join.
- Full join returns every row from both tables and fills the gaps with
NULLwherever one side has no match.
Not every database supports every join type. MySQL, for example, has no direct FULL JOIN keyword, so developers combine a left join and a right join to get the same result.
A Customer and Orders Example
Imagine a customers table with three people: Ana, Ben and Chloe. The orders table holds two orders from Ana and one from Ben. Chloe has not ordered yet.
An inner join between the two tables returns three rows: two for Ana and one for Ben. Chloe is left out because nothing matches her.
A left join that starts from the customers table returns four rows. Ana and Ben appear with their orders, and Chloe appears once with empty order columns. This suits a report that must list every customer, including those who have never bought anything.
Frequently asked questions
Does a left join return more rows than an inner join?
It can. A left join keeps every row from the left table, including rows with no match, while an inner join keeps only the matching pairs. If every left row matches exactly one right row, both joins return the same number of rows.
Can SQL joins make a database slow?
Yes, joins can be slow on large tables. Without an index on the columns being matched, the database may have to compare many rows against many others. Indexing join columns, such as foreign keys, is one of the most common ways to speed them up. Read about how indexes speed up lookups for more detail.