What will MySQL return if I don't use the keyword 'RIGHT' or 'LEFT' when I write a RIGHT JOIN or LEFT JOIN query?

In both cases, when the "RIGHT" or "LEFT" keywords are not used in the query, MySQL will return the results as an INNER JOIN query. This is because the only difference between RIGHT, LEFT, and INNER JOIN is the RIGHT or LEFT keyword. To understand it, let's take an example of two tables named tbl_1 and tbl_2, which have the following data -

mysql> Select * from tbl_1;
+----+--------+
| Id | Name   |
+----+--------+
| 1  | Gaurav |
| 2  | Rahul  |
| 3  | Raman  |
| 4  | Aarav  |
+----+--------+
4 rows in set (0.00 sec)

mysql> Select * from tbl_2;
+----+---------+
| Id | Name    |
+----+---------+
| A  | Aarav   |
| B  | Mohan   |
| C  | Jai     |
| D  | Harshit |
+----+---------+
4 rows in set (0.00 sec)

Now, the query for RIGHT JOIN using keyword RIGHT can be as follows -

mysql> SELECT tbl_1.id,tbl_2.id FROM tbl_1 RIGHT JOIN tbl_2 ON tbl_1.name = tbl_2.name;
+------+----+
| id   | id |
+------+----+
| 4    | A  |
| NULL | B  |
| NULL | C  |
| NULL | D  |
+------+----+
4 rows in set (0.00 sec)

Now, in the following query we are not using the keyword RIGHT -

mysql> Select tbl_1.id,tbl_2.id FROM tbl_1 JOIN tbl_2 ON tbl_1.name = tbl_2.name;
+----+----+
| id | id |
+----+----+
| 4  | A  |
+----+----+
1 row in set (0.00 sec)

From the above result set we can observe the difference, without using the keyword 'RIGHT', MySQL will treat it as INNER JOIN the query and return the results accordingly.

Now, the query for LEFT JOIN using the keyword LEFT can be as follows-

mysql> SELECT tbl_1.id,tbl_2.id FROM tbl_1 LEFT JOIN tbl_2 ON tbl_1.name = tbl_2.name;
+----+------+
| id | id   |
+----+------+
| 1  | NULL |
| 2  | NULL |
| 3  | NULL |
| 4  | A    |
+----+------+
4 rows in set (0.02 sec)

Now, in the following query we do not use the keyword LEFT-

mysql> Select tbl_1.id,tbl_2.id FROM tbl_1 JOIN tbl_2 ON tbl_1.name = tbl_2.name;
+----+----+
| id | id |
+----+----+
| 4  | A  |
+----+----+
1 row in set (0.00 sec)

From the above We can observe the difference in the result set, without using the keyword "LEFT", MySQL treats it as an INNER JOIN query and returns the results accordingly.

The above is the detailed content of What will MySQL return if I don't use the keyword 'RIGHT' or 'LEFT' when I write a RIGHT JOIN or LEFT JOIN query?. For more information, please follow other related articles on the PHP Chinese website!