Lezioni di PHP - Lezione 3.6 - Lavorare con il database MySQL. Tipi di operatori JOIN.

In MySQL, the selection using JOIN can be done in different ways. We will try to consider all these types of queries. Here is a list of all queries involving JOIN:

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

And here is an illustration of these types of JOIN:

I will attach to the article the files from our site, among which there will be join.php, where I will display all records using different JOIN operators.

INNER JOIN

We’ll start with this operator INNER JOIN because this operator works by default if you simply write JOIN in the query. This operator selects all records from two tables where the condition after the ON operator is met. We have two tables: Files and Messages:

Table Messages:

 Table Files:

The query with JOIN will be the following:

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

As a result, these records will be displayed

That is, where fid matches, MySQL will output those rows.

LEFT JOIN

With LEFT JOIN we output all records from the table on the left (in our case, Messages), including those records where these fid values exist in the Files table.

Table Messages:

 Table Files:

The query with LEFT JOIN will be the following:

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

As a result, these records will be displayed

LEFT JOIN is useful when we output all message records, and whether or not an attached file exists we check later via PHP.

LEFT JOIN without intersections with the right table

LEFT JOIN outputs all records from the left table except those where fid matches the right table.

Table Messages:

 Table Files:

The query with LEFT JOIN without intersections will be the following:

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

As a result, we will get the following selection:

LEFT JOIN without intersections will be useful when we output all records without attached files.

RIGHT JOIN

RIGHT JOIN outputs all records from the right table; if there are intersections, then data from the left table are displayed.