SQL Joins on Multiple Keys

The SQL JOIN allows us to combine data from multiple tables based on one or more common columns, called join keys. When performing a join on multiple keys, we can specify multiple join conditions in the ON clause of the SQL query.

Here is an example of an SQL query that joins two tables on two common keys:

SELECT * 
FROM table1  
INNER JOIN table2 
ON table1.key1 = table2.key1 AND table1.key2 = table2.key2;

This SQL query uses an INNER JOIN to combine data from table1 and table2 based on two common keys, key1, and key2. The ON clause specifies two join conditions separated by the AND operator. The AND specifies that the values of key1 and key2 must match in both tables. If they are true, that row will be in the result set.

For example, suppose you have these two tables:

bookshop_customers

bookshop_orders

You could use, CustomerID and City to join these tables and display the CustomerID, FirstName, LastName, City, and Month columns together. To accomplish this, you would use the following SQL statement:

SELECT bookshop_customers.CustomerID, FirstName, LastName, City, Month 
FROM bookshop_customers 
JOIN sandbox.bookshop_orders 
ON bookshop_customers.CustomerID = bookshop_orders.CustomerID AND bookshop_customers.City ='New York';

This SQL statement will return the following result:

We joined the tables based on CustomerID and City and specified that we only want to see orders for customers located in New York.

In this example, the JOIN operator combines the Customers and Orders tables and specifies the condition for the join using the ON keyword. Using multiple keys in the join condition ensures that the join is as precise as possible and only returns the data we need.

Another situation where SQL Joins on Multiple Keys may help

Let’s say you are a data analyst working for a company that has a large customer database, and you are asked to retrieve the contact information for a customer named “John Smith.” However, many customers in the database have the same name, and you don’t know which one is the correct John Smith.

In this scenario, you could join the “Customers” table with the “Orders” table based on multiple keys, such as the customer’s name, city, and order date, to find the specific John Smith you are looking for. This way, you can retrieve the additional information you need, such as the customer’s address, email, phone number, and more.