JOIN

MySQL

Example

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

Output

| order_id | customer_name |
|----------|---------------|
| 1        | John Doe      |
| 2        | Jane Smith    |
| 3        | Mary Johnson  |

Explanation

In the given example, the INNER JOIN keyword is used to select records that have matching values in both tables orders and customers. The SELECT statement returns the order_id from the orders table and the customer_name from the customers table.

PostgreSQL

Example

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

Output

 order_id | customer_name
----------+---------------
     1    | John Doe
     2    | Jane Smith
     3    | Bob Johnson
     4    | Alice Johnson

Explanation

In the given SQL query, an INNER JOIN is performed between the orders and customers tables. The INNER JOIN matches rows from both tables where the condition, defined by orders.customer_id = customers.customer_id, is met. The result is a set of combined rows from both tables where there are matching values in the customer_id field. The SELECT clause then specifies that we want to retrieve the order_id from the orders table and customer_name from the customers table for each of these matched records.

SQL Server

Example

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

Output

| OrderID | CustomerName | OrderDate   |
|---------|--------------|-------------|
|   1     |   Smith      | 2017-07-04  |
|   2     |   Jones      | 2017-08-05  |
|   3     |   White      | 2017-09-06  |

Explanation

The INNER JOIN keyword selects records that have matching values in both tables. In the provided example, it retrieves the corresponding ‘OrderID’, ‘CustomerName’ and ‘OrderDate’ from the ‘Orders’ and ‘Customers’ tables where the ‘CustomerID’ is the same in both tables.

Oracle

Example

SELECT e.employee_id, e.first_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

Output

EMPLOYEE_ID	FIRST_NAME	DEPARTMENT_NAME
100	Steven	Executive
101	Neena	Executive
102	Lex	Executive

Explanation

The JOIN command combines rows from two or more tables. In this case, it is used to combine rows from the ‘employees’ and ‘departments’ tables. The ON keyword is used to specify the matching column between the two tables, which is the ‘department_id’. The result of the query is a list of employees’ ID, first names and the names of the departments they work in.

SQLite

Example

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
JOIN Orders
ON Customers.CustomerID=Orders.CustomerID;

Output

| CustomerName   | OrderID |
|----------------|---------|
| Alfreds        |  10308  |
| Ana Trujillo    |  10309  |
| Antonio Moreno |  10310  |

Explanation

In the provided SQL example, a JOIN operation is used to combine rows from two tables, Customers and Orders. The ON keyword defines the condition for the join: matching CustomerID between the two tables. The SELECT statement returns a result table with CustomerName from Customers and OrderID from Orders.