SQL JOIN

Next Page >>
sql tutorial

SQL Tutorial » SQL JOIN

What is a join SQL statement?

A JOIN SQL statement is used to combine rows from two or more database tables.

The SQL JOIN clause takes records from two or more tables in a database and combines it together.

There are different types of joins available in SQL −
1. INNER JOIN − returns rows when there is a match in both tables.
2. LEFT JOIN − returns all rows from the left table, even if there are no matches in the right table.
3. RIGHT JOIN − returns all rows from the right table, even if there are no matches in the left table.
4. FULL JOIN − returns rows when there is a match in one of the tables.
5. SELF JOIN − is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.
CARTESIAN JOIN − returns the Cartesian product of the sets of records from the two or more joined tables.

Syntax:

SELECT table_1.table_1_ID, table_2.table_2_Name, table_1.table_1_Date
FROM table_1
INNER JOIN table_2 ON table_1.table_1_ID=table_2.teble_2_ID;

Let's look at the "Orders" table: We will have four columns as: "Order_ID", "Customer_ID", "Country", and "Order_date":

Order_ID	Customer_ID	Country	Order_date
11	2	USA	09/11/2017
12	3	France	23/01/2019
13	5	USA	15/06/2018
14	102	Spain	18/03/2011
15	218	SUA	27/11/2020

Selection from the "Customers" table:

Customer_ID	Customer_Name	Country	Contact_Name
1	Kent Ford	USA	Martin Chris
2	Joveau Pierre	France	Paul De Lumond
3	Anderson Nime	USA	Jojo K
4	Martínez Alexandro	Spain	Udriste John
5	Parker Table	SUA	Vala A

Note: "Customer_ID" column in the "Orders" table refers to the "Customer_ID" in the "Customers" table. The relationship between the two tables above is the "CustomerID" column.

How do I JOIN two tables in SQL - Examples

Let's JOIN two tables Orders and Customers::

SELECT Orders.Order_ID, Customers.Customer_Name, Orders.Order_date
FROM Orders
INNER JOIN Customers ON Orders.Customer_ID=Customers.Customer_ID;

Output displayed:

Order_ID	Customer_Name	Order_date
2	Joveau Pierre	09/11/2017
3	Anderson Nime	23/01/2019
5	Parker Table	15/06/2018

Tags: sql join:3 tables, types, multiple tables, where, on multiple conditions, vs inner join, vs left join, explained, firstmatch only, table with itself
What is a join SQL statement?
Can I join more than 2 tables in SQL?
How do I join two tables in SQL?
Can 3 tables be joined in SQL?

SQL operators to JOIN tables

Several operators can be used to join tables, such as =, <, >, <>, <=, >=, !=, BETWEEN, LIKE, and NOT; they can all be used to join tables.

SQL JOIN - WHERE clause vs. ON clause

WHERE clause and ON clause are not the same.

WHERE

SELECT *
FROM Orders
LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID
WHERE Orders.ID = 12345

AND

SELECT *
FROM Orders
LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID
AND Orders.ID = 12345

The first will return an order and its lines, if any, for order number 12345. The second will return all orders, but only order 12345 will have any lines associated with it.

sql join:3 tables, types, multiple tables, where, on multiple conditions, vs inner join, vs left join, explained, firstmatch only, table with itself
What is a join SQL statement?
Can I join more than 2 tables in SQL?
How do I join two tables in SQL?
Can 3 tables be joined in SQL?
SQL JOIN - sql tutorial

Online Editor

ONLINE EDITOR

news templates

COLOR PICKER

news templates

This tool makes it easy to create, adjust, and experiment with custom colors for the web.

HTML Templates

Magnews2 is a modern and creative free magazine and news website template that will help you kick off your online project in style.

CSS HTML Layout

Find here examples of creative and unique website layouts.

Free CSS HTML Menu

Find here examples of creative and unique website CSS HTML menu.