How Do You Use the Where Clause to Join Multiple Tables in an SQL Query

So far we have looked at simple SQL statements applied on one table. But we are using relational databases, so we will need to know how to access data from multiple tables using SQL.

Understanding the Basics of SQL WHERE Clause

Use Aliases with Multiple Tables

There are two reasons that using aliases for tables is a good idea:

we will need to define which table the keys are from, as keys are in both the main table (primary key), and the junction table (foreign key).
Columns will appear in multiple tables and SQL needs to know ‘which’ table the column is from.

Examples of aliases for tables

from student s, registration r

s.student_id = r.student_id

SELECT s.student_id
FROM student s, registration r
WHERE s.student_id = r.student_id

SQL for Multiple Tables

We can access data from multiple tables using SQL select statements in the where clause.

We use the equals sign (=) to highlight the relationship between the primary key and foreign key.

SELECT student_name
FROM student s, registration r
WHERE s.student_id = r.student_id

The syntax and format of the select statement and the where clause can be used for all the tables connected to the junction table.

SELECT c.course_code
FROM course c, registration r
WHERE c.course_code = r.course_code

SQL Select Distinct (remove duplicates)

In some results the data will be a list that contains duplicates that we do not need.

We can remove duplicate values using distinct.

SELECT DISTINCT student_name
FROM student s, registration r
WHERE s.student_id = r.student_id

SELECT distinct c.course_code, course_name, course_date
FROM course c, registration r
WHERE c.course_code = r.course_code

How to Structure Complex SQL Queries

SQL for Multiple Tables using the AND clause

If we normally use the where clause to identify selected rows, then what do we do when we have the table join in the where clause?

We use the WHERE and the AND clause.

SELECT student_name, course_code
FROM student s, registration r
WHERE s.student_id = r.student_id
AND s.student_id = 652415503

Again we use the same SQL code for the different tables in our multiple table SQL select statement.

SELECT c.course_code, course_name, course_date, r.student_id
FROM course c, registration r
WHERE c.course_code = r.course_code
AND c.course_code = 888342

If you try these SQL statements they don’t give you all the information you want in the format you want. what happens if you want to see the courses for students, or, the students on courses?

SQL for Multiple Tables using multiple AND clauses

SELECT c.course_code, course_name, course_date, r.student_id, student_name
FROM course c, registration r, student s
WHERE c.course_code = r.course_code
AND c.course_code = 888342
AND s.student_id = r.student_id
ORDER BY s.student_id

All three tables in the student database are linked using the foreign keys seen in both the WHERe clause and the final AND clause.

We also have another WHERE clause to specify the course code ‘888342’. Weonly use one WHERE clause, but we can use multiple AND clauses that act the same as if they were WHERE clauses.

Notice the extensive use of aliases to identify which table the column is from. In the select, from, where, and, and the order by lines use aliases.

SQL for Multiple Tables and Multiple Order By Clauses

SELECT c.course_code, course_name, course_date, r.student_id, student_name
FROM course c, registration r, student s
WHERE c.course_code = r.course_code
AND s.student_id = r.student_id
ORDER BY c.course_date, c.course_code,s.student_id

In this example, we want to locate the early courses and select them first before the second-semester courses. We also want to order the courses and the students within the courses. We can achieve this by using the order by clause in order of preference.

Common Scenarios for SQL WHERE Clause in Relational Databases

SQL Aggregate Functions

SQL can use arithmetic to view not only database data but also the data after it has some action applied to it. For example, we might want to alter the data (e.g. multiply it, data * 1.1), see the average (AVG), or the sum (SUM), or count the amount (COUNT).

With SQL aggregate select statements we often create a new value and therefore can use an alias using the keyword ‘AS’

SELECT COUNT(s.student_id) AS student_count
FROM registration r, student s
WHERE r.student_id = s.student_id

SQL Group By Having

More advance SQL can group the data using the GROUP BY clause and the HAVING clause. This is more advanced than this introductory course but included to show what SQL statements can do.

SELECT c.course_name, c.course_date, COUNT(s.student_id) AS student_count
FROM registration r, student s, course c
WHERE r.course_code = c.course_code
AND r.student_id = s.student_id
GROUP BY c.course_name, c.course_date
HAVING COUNT(s.student_id) > 0
ORDER BY c.course_date, c.course_name

Try the SQL statements on your student database to see what the output loos like.

SELECT s.student_name, COUNT(r.course_code) AS course_count
FROM registration r, student s
WHERE r.student_id = s.student_id
GROUP BY s.student_name
HAVING COUNT(r.course_code) > 0
ORDER BY s.student_name;

SQL INSERT UPDATE and DELETE

We have only used insert and select to enter and see our data but we can change it using UPDATE or remove it using DELETE.

Try this example where we insert data that has the wrong date format.

There are two options: First, update the data to the correct value, or, delete the incorrect data record completely.

INSERT INTO course (course_code, course_name, course_date) VALUES (888123, ‘Data Analysis Basics’, ‘2024-02’);

UPDATE course SET course_date = ‘2024-2’ WHERE course_code = 888123;

DELETE FROM course WHERE course_code = 888123;

Continue to Database Design Considerations

final part of lesson 3: Database Design considerations

return to the stages to create simple database

FAQ

1. Can we use multiple values in WHERE clause in SQL?
Yes, you can use multiple values in the WHERE clause by combining conditions that must be met using logical operators such as AND, OR, or the IN operator. This approach is especially useful for filtering records efficiently in SQL queries.

2. How to write multiple conditions in SQL query?
To write multiple conditions in SQL, you can use the following example syntax:

SELECT *
FROM customers
WHERE country = ‘USA’
AND age > 25;
This query retrieves records where both conditions are satisfied.

3. Can you put an AND in a WHERE clause SQL?
Yes, the AND operator is used to specify that both conditions specified in the query must be met for a record to be included in the result set. For instance:

SELECT * FROM orders WHERE status = ‘Shipped’ AND customer_id = 101;

4. How to use multiple case conditions in SQL?
To use multiple case conditions in SQL, you can write the following query:

SELECT product_id, CASE WHEN quantity > 50 THEN ‘Bulk Order’ WHEN quantity BETWEEN 20 AND 50 THEN ‘Medium Order’ ELSE ‘Small Order’ END AS order_type FROM products;

This query evaluates each record and categorizes it based on the conditions specified.

Key Features of SQL Multiple WHERE Clause

Combine Conditions: The SQL WHERE clause can filter records based on multiple conditions using AND, OR, and parentheses for order of evaluation.
Null Values Handling: Conditions can evaluate null values using IS NULL or IS NOT NULL expressions.
Precedence of Operators: Use parentheses to dictate the order in which conditions are evaluated and ensure the correct logic is applied.
Optimization Tips: To optimize your queries, ensure that indexes are used for frequently filtered fields and avoid unnecessary conditions in complex queries.
Default and Data Types: Ensure compatibility between the data types used in conditions to avoid query errors.