PHP Lessons - Lesson 3.6 - Working with MySQL DB. Types of JOIN operator.

In MySQL, JOIN queries can be written in several different ways. We'll try to cover all of them. Here's a list of JOIN query types:

1. INNER JOIN
2. LEFT JOIN
3. LEFT JOIN without matches in the right table
4. RIGHT JOIN
5. RIGHT JOIN without matches in the left table
6. FULL OUTER JOIN
7. FULL OUTER JOIN where the left or right table is empty

Here’s an illustration of these types of JOINs:

Attached to the article are files for our site, including join.php where I’ll show all the records using different JOIN operators.

INNER JOIN

This is the default JOIN operation in MySQL when just JOIN is written. It selects all records from two tables where the condition following the ON clause is true. We have two tables: Files and Messages.

Messages table:

Files table:

Query:

SELECT * FROM Messages INNER JOIN Files ON Messages.fid=Files.fid

Result:

MySQL returns only rows where fid matches in both tables.

LEFT JOIN

LEFT JOIN returns all rows from the left table (Messages), along with matched rows from the right table (Files). If no match, NULLs are returned.

Query:

SELECT * FROM Messages LEFT JOIN Files ON Messages.fid=Files.fid

Result:

This is useful when you want to display all messages, whether they have an attached file or not.

LEFT JOIN without matches in the right table

This type returns only rows from the left table that have no corresponding row in the right table.

SELECT * FROM Messages LEFT JOIN Files ON Messages.fid=Files.fid WHERE Files.fid IS NULL

Result:

Useful when displaying messages without attached files.

RIGHT JOIN

RIGHT JOIN returns all records from the right table and the matched records from the left table.

SELECT * FROM Messages RIGHT JOIN Files ON Messages.fid=Files.fid

Result:

Use it when displaying all files, regardless of usage.

RIGHT JOIN without matches in the left table

Returns only records from the right table that have no corresponding record in the left table.

SELECT * FROM Messages RIGHT JOIN Files ON Messages.fid=Files.fid WHERE Messages.fid IS NULL

Result:

Useful for finding unused files.

FULL OUTER JOIN

MySQL does not support the FULL OUTER JOIN natively. You can emulate it using UNION of LEFT and RIGHT JOINs.

SELECT * FROM Messages LEFT JOIN Files ON Messages.fid = Files.fid
UNION
SELECT * FROM Messages RIGHT JOIN Files ON Messages.fid = Files.fid

Result:

Not often used, but useful when you want to see all unmatched rows from both tables.

FULL OUTER JOIN without matches

Even more exotic: FULL OUTER JOIN showing only non-matching rows. Again, not supported natively in MySQL, so we emulate it:

SELECT * FROM Messages LEFT JOIN Files ON Messages.fid = Files.fid WHERE Files.fid IS NULL 
UNION 
SELECT * FROM Messages RIGHT JOIN Files ON Messages.fid = Files.fid WHERE Messages.fid IS NULL

Result:

And that’s about it. In future lessons, we'll write more complex queries involving multiple tables.