How to join 2 SQL tables with PHP? Examples and visualisations

Often you need to join data from 2 tables. You can do it with several SQL queries and PHP, or just use JOIN functions. The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables. Most popular joins are INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN. It's always good to know the different.

SQL JOIN or INNER JOIN

Return rows when there is at least one match in both tables

Example:

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name

Visualisation:

INNER JOIN

SQL LEFT JOIN

Return all rows from the left table, even if there are no matches in the right table

Example:

SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

Visualisation:

LEFT JOIN

SQL RIGHT JOIN

Return all rows from the right table, even if there are no matches in the left table

Example:

SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

Visualisation:

RIGHT JOIN

SQL FULL JOIN

Return rows when there is a match in one of the tables

Example:

SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name

Visualisation:

FULL JOIN