When Are LEFT JOIN and RIGHT JOIN Queries Interchangeable in SQL?

SQL's LEFT JOIN and RIGHT JOIN: When are they equivalent?

LEFT JOIN and RIGHT JOIN in SQL merge rows from two tables based on matching values. While functionally similar, a key distinction exists.

Let's illustrate:

CREATE TABLE Table1 (id INT, Name VARCHAR(10));
CREATE TABLE Table2 (id INT, Name VARCHAR(10));

INSERT INTO Table1 (id, Name) VALUES
(1, 'A'),
(2, 'B');

INSERT INTO Table2 (id, Name) VALUES
(1, 'A'),
(2, 'B'),
(3, 'C');

These queries:

SELECT *
FROM Table1
LEFT JOIN Table2 ON Table1.id = Table2.id;

SELECT *
FROM Table2
RIGHT JOIN Table1 ON Table1.id = Table2.id;

yield identical results. Both match rows from Table1 with corresponding rows in Table2 using the id field. LEFT JOIN includes all rows from Table1, while RIGHT JOIN does the same for Table2.

However, consider this:

SELECT *
FROM Table2
LEFT JOIN Table1 ON Table1.id = Table2.id;

or its equivalent:

SELECT *
FROM Table1
RIGHT JOIN Table2 ON Table1.id = Table2.id;

The output differs. This returns all rows from Table2 plus any matching rows from Table1. This is an "outer join," where rows from one table are included even without matches in the other. The key is that the left or right designation dictates which table's rows are guaranteed to be fully included in the result set, regardless of matching rows in the other table. Therefore, simple swapping of tables in the query doesn't always produce the same result. The interchangeability depends entirely on the data and the desired outcome.

The above is the detailed content of When Are LEFT JOIN and RIGHT JOIN Queries Interchangeable in SQL?. For more information, please follow other related articles on the PHP Chinese website!