In SQL, the JOIN clause is used to combine rows from two or more tables based on a related column between them. When combined with the SELECT statement, JOIN enables you to retrieve data from multiple tables as a single result set.

There are different types of JOIN operations, but the most commonly used are:

  1. INNER JOIN: Returns only the rows where there is a match between the related columns in both tables.

  2. LEFT JOIN(simplied JOIN): Returns all the rows from the left table, and the matched rows from the right table. If there is no match in the right table, NULL values are returned.

  3. RIGHT JOIN: Returns all the rows from the right table, and the matched rows from the left table. If there is no match in the left table, NULL values are returned.

  4. FULL OUTER JOIN: Returns all the rows from both tables, including the unmatched rows, and NULL values are used for the missing data.

Here's an example of a SELECT statement with an INNER JOIN:

SELECT users.name, articles.title
FROM users
INNER JOIN articles
ON users.user_id = articles.user_id;

This query selects the name column from the users table, and the title column from the articles table. The INNER JOIN is used to combine the rows from both tables based on the matching user_id columns, and only the rows that have a match in both tables are returned.

To perform a LEFT JOIN, you can replace the INNER JOIN keyword with LEFT JOIN, and to perform a RIGHT JOIN, you can replace it with RIGHT JOIN. For a FULL OUTER JOIN, you can use FULL OUTER JOIN or LEFT JOIN and RIGHT JOIN together.

The JOIN clause is a powerful feature in SQL that allows you to combine data from multiple tables to answer more complex questions about your data.

TypeOfJoin.png