Relational Database Queries: INNER JOINs with Multiple Columns

Inner Joins and Databases

In relational databases, tables store information organized into columns and rows. An INNER JOIN is a powerful SQL operation that combines data from two or more tables based on a shared characteristic. This allows you to retrieve related information from different tables in a single query.

Joining on Multiple Columns

When you have tables with composite keys (primary keys made of more than one column) or foreign keys referencing those composite keys, you can join them based on multiple columns to ensure accurate results. Here's the syntax:

SELECT column1, column2, ...
FROM table1
INNER JOIN table2
ON table1.columnA = table2.columnB
AND table1.columnC = table2.columnD
AND ... (more join conditions)

• SELECT: This clause specifies the columns you want to retrieve from the tables.
• FROM: This clause identifies the tables you're joining.
• INNER JOIN: This clause defines the type of join.
• ON: This clause specifies the join condition. Here's where you compare multiple columns between the tables using the AND operator to ensure rows match on all specified criteria.
  • table1.columnA, table1.columnC, etc.: These represent columns from the first table used in the join condition.

Example

Imagine you have two tables: customers and orders. The customers table has columns for customer_id, name, and city, while the orders table has columns for order_id, customer_id, and product. To find all orders placed by customers in a specific city, you can use an INNER JOIN on multiple columns:

SELECT c.name, o.order_id, o.product
FROM customers c
INNER JOIN orders o
ON c.customer_id = o.customer_id
AND c.city = 'New York';

Example 1: Joining Books and Authors

This example joins two tables, books and authors, based on the author_id column:

SELECT b.title, a.name
FROM books b
INNER JOIN authors a
ON b.author_id = a.id;

This query will return a list of book titles along with the author's name for each book.

Example 2: Joining Courses and Enrollments (Composite Key)

This example joins a courses table with an enrollments table where the enrollment table has a composite key of student_id and course_id:

SELECT c.name AS course_name, s.name AS student_name
FROM courses c
INNER JOIN enrollments e
ON c.id = e.course_id
AND e.student_id = 10;

Here, we use aliases (course_name and student_name) to improve readability. This query retrieves the course name and student name for a specific student identified by their student_id (10 in this case).

Example 3: Joining Products and Orders with Foreign Keys

This example joins a products table with an orders table where the orders table has a foreign key product_id referencing the primary key in the products table:

SELECT p.name AS product_name, o.quantity
FROM products p
INNER JOIN orders o
ON p.id = o.product_id
AND o.status = 'shipped';

This query retrieves the names of shipped products and their corresponding quantities from the orders table.

Here's a brief illustration of these alternatives:

Subquery Example:

Imagine finding all customers from 'New York' and then retrieving their orders. You could achieve this with a subquery:

SELECT *
FROM orders o
WHERE o.customer_id IN (
  SELECT customer_id
  FROM customers
  WHERE city = 'New York'
);

WHERE Clause with Multiple Conditions Example:

Let's say you want the book title and author name for a book with a specific ISBN (International Standard Book Number) and title:

SELECT b.title, a.name
FROM books b
INNER JOIN authors a ON b.author_id = a.id
WHERE b.isbn = '123-456-7890' AND b.title = 'The Great Gatsby';

This achieves the same result as an INNER JOIN with multiple columns in the ON clause, but becomes less manageable for more complex conditions.

Temporary Table/CTE Example:

For a complex join involving multiple columns and filtering, you could create a temporary table or CTE to pre-process data:

WITH filtered_customers AS (
  SELECT customer_id
  FROM customers
  WHERE city = 'New York' AND loyalty_points > 100
)
SELECT c.name, o.order_id, o.product
FROM filtered_customers fc
INNER JOIN orders o ON fc.customer_id = o.customer_id;

This pre-filters customers based on city and loyalty points in the CTE, then joins with the orders table.