SQL JOIN Statement with Example JOIN Syntax

The JOIN statement in SQL is used to combine rows from two or more tables based on a related column between them. It allows you to retrieve data from multiple tables in a single query.

Types of SQL Join

There are several types of JOINs in SQL: 

1. INNER JOIN, 
2. OUTER JOIN, 
3. CROSS JOIN, and 
4. SELF JOIN.

Basic Syntax of JOIN statement

SELECT *

FROM table1

JOIN table2

ON table1.column = table2.column;

This will return all rows from both tables where the column value is the same. The result set will include all columns from both tables.

You can also use a JOIN statement to return only specific columns, like this:

SELECT table1.column1, table2.column2

FROM table1

JOIN table2

ON table1.column = table2.column;

This will return the column1 value from table1 and the column2 value from table2 for all rows where the column value is the same.

You can also specify a type of JOIN, such as INNER JOIN or OUTER JOIN, using the INNER JOIN or LEFT JOIN keywords, like this:

SELECT *

FROM table1

INNER JOIN table2

ON table1.column = table2.column;

SELECT *

FROM table1

LEFT JOIN table2

ON table1.column = table2.column;

The INNER JOIN will return only rows where there is a match in both tables, while the LEFT JOIN will return all rows from the left table and any matching rows from the right table. If there is no match, NULL values will be returned for the right table’s columns.

A Simple SQL Example JOIN Statement:

SELECT *

FROM users

JOIN orders

ON users.id = orders.user_id;

This statement will retrieve all rows from the users and orders tables and return a result set that includes all the columns from both tables. The JOIN clause specifies that the two tables should be joined on the id column of the users table and the user_id column of the orders table.

A More Complex SQL Example JOIN Statement:

SELECT users.name, orders.id, products.name

FROM users