Description

SQL INNER JOIN can be used to join two tables, which returns the rows having a match between the joined tables.

  • It is the most commonly used type of join.
  • It can be implemented on SELECT, UPDATE, and DELETE statements.
  • The table joins can be created using the WHERE clause by comparing the related columns between the joined tables.

Syntax

Here is the basic syntax of a SELECT statement with an INNER JOIN.

  • The table1_name and table2_name represents the first and second table names.
  • The column_list represents the column names from either or both the tables.
  • The column1_name and column2_name are the columns from the first and second tables, which are used for creating an INNER JOIN.
SELECT column_list
FROM table1_name
INNER JOIN table2_name ON table1.column1_name = table2.column2_name;

Table Data

Consider the below data in the orders, customers, and shippers tables, with the below observations.

  • Tables orders and customers are relationally connected via customer_id column.
  • Tables orders and shippers are relationally connected via shipper_id column.

Orders Table Data

order_id order_date order_value customer_id shipper_id
10250 2014-07-05 1807.5 3 1
10251 2014-07-15 1159 2 2
10252 2014-07-18 2822 1 3
10253 2014-07-19 2575.3 3 2
10254 2014-07-24 1256.25 1 3
10255 2014-07-31 9247.5 5 1

Customers Table Data

customer_id customer_name address  city country postal_code
1 Maria Anders Obere Str. 57 Berlin Germany 12209
2 Fran Wilson C/ Araquil, 67 Madrid Spain 28023
3 Dominique Perrier 25, rue Lauriston Paris France 75016

Shipper Table Data

shipper_id shipper_name phone
1 Speedy Express (503) 555-9831
2 United Package (503) 555-3199
3 Federal Shipping (503) 555-9931

Inner Join

The below SQL statement fetches data from both the tables as mentioned below.

  • Returns only rows that satisfy the INNER JOIN condition.
  • Returns data for customer name from the customers table.
  • Returns data for all other columns from the orders table.

Run this on IDE

SELECT order_id, order_date, order_value, customer_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;

After successful execution, the output contains data from both the joined tables as shown below.

order_id order_date order_value customer_name
10250 2014-07-05 1807.5 Dominique Perrier
10251 2014-07-15 1159 Fran Wilson
10252 2014-07-18 2822 Maria Anders
10253 2014-07-19 2575.3 Dominique Perrier
10254 2014-07-24 1256.25 Maria Anders

Inner Join All Columns

The below SQL statement fetches data from both the tables as mentioned below.

  • Returns only rows that satisfy the INNER JOIN condition.
  • The SELECT * returns all the columns from both the tables, without repeating the joined columns.

Run this on IDE

SELECT *
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;

After successful execution, the output contains data from both the joined tables as shown below.

order_id order_date order_value customer_id supplier_id customer_name address city country postal_code
10250 2014-07-05 1807.5 3 1 Dominique Perrier 25, rue Lauriston Paris France 75016
10251 2014-07-15 1159 2 2 Fran Wilson C/ Araquil, 67 Madrid Spain 28023
10252 2014-07-18 2822 1 3 Maria Anders Obere Str. 57 Berlin Germany 12209
10253 2014-07-19 2575.3 3 2 Dominique Perrier 25, rue Lauriston Paris France 75016
10254 2014-07-24 1256.25 1 3 Maria Anders Obere Str. 57 Berlin Germany 12209

Inner Join More Tables

The below SQL statement fetches data from three tables using multiple INNER JOIN keywords as shown below.

  • Returns only rows that satisfy all the INNER JOIN conditions in case of more than two tables.
  • Returns data for customer name from the customers table.
  • Returns data for shipper name from the shippers table.
  • Returns data for all other columns from the orders table.

Run this on IDE

SELECT order_id, order_date, order_value, customer_name, shipper_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id
INNER JOIN shippers ON orders.shipper_id = shippers.shipper_id;

After successful execution, the output contains data from all the joined tables as shown below.

order_id order_date order_value customer_name supplier_name
10250 2014-07-05 1807.5 Dominique Perrier Speedy Express
10251 2014-07-15 1159 Fran Wilson United Package
10252 2014-07-18 2822 Maria Anders Federal Shipping
10253 2014-07-19 2575.3 Dominique Perrier United Package
10254 2014-07-24 1256.25 Maria Anders Federal Shipping

Overall

We now know how to create update records on a table using the UPDATE statement.

Related Links