What is the difference between INNER JOIN, LEFT JOIN and RIGHT JOIN in SQL?

Once I came across the question: "what is a right table and what is a left table in SQL?"

In SQL, the terms "left table" and "right table" refer to the order in which tables are listed in a JOIN clause. His order determines how the join operates, especially in LEFT JOIN and RIGHT JOIN scenarios.

Determining the Left and Right Tables:

• Left Table: The table that appears first in the JOIN clause.
• Right Table: The table that appears second in the JOIN clause.

ELECT columns
FROM table1
LEFT JOIN table2 ON table1.id = table2.id;

In this above query:

• table1 is the left table.
• table2 is the right table.

Understanding LEFT JOIN and RIGHT JOIN:

• LEFT JOIN: Returns all records from the left table and the matched records from the right table. If there's no match, the result is NULL on the side of the right table.
• RIGHT JOIN: Returns all records from the right table and the matched records from the left table. If there's no match, the result is NULL on the side of the left table.

The designation of "left" and "right" is purely based on the order of tables in your SQL statement. By rearranging the order of tables and switching between LEFT JOIN and RIGHT JOIN, you can achieve the same results. However, LEFT JOIN is more commonly used in practice.

---

Now, what is the difference between JOIN, LEFT JOIN and RIGHT JOIN in SQL?

In SQL, joins are used to combine rows from two or more tables based on related columns. The primary types of joins are:

• INNER JOIN: Returns only the rows where there is a match in both tables.
• LEFT JOIN (LEFT OUTER JOIN): Returns all rows from the left table and the matched rows from the right table. If there's no match, the result is NULL on the side of the right table.
• RIGHT JOIN (RIGHT OUTER JOIN): Returns all rows from the right table and the matched rows from the left table. If there's no match, the result is NULL on the side of the left table.

Syntax examples:

  ELECT columns
  FROM table1
  INNER JOIN table2
  ON table1.common_column = table2.common_column; 

  ELECT columns
  FROM table1
  LEFT JOIN table2
  ON table1.common_column = table2.common_column;

  ELECT columns
  FROM table1
  RIGHT JOIN table2
  ON table1.common_column = table2.common_column;