A predicate is a SQL expression that asks whether a condition is true, false, or unknown. Think of it as a test applied to data: rows that satisfy the needed predicates can be included in a query result. Predicates commonly connect related rows in a JOIN and narrow results in WHERE; filtering conditions can also appear in FROM and HAVING. section 8.2.1 section 17.1.4
For example, an order record may contain a customer ID but not the customer’s name. A JOIN predicate compares the ID in each table so SQL can find the matching customer row. A WHERE predicate then selects the particular order. When both conditions must hold, only rows that match both are returned. section 8.2.1
SELECT
orderheader.CustomerID,
customer.FirstName,
customer.LastName
FROM orderheader
JOIN customer
ON orderheader.CustomerID = customer.CustomerID
WHERE orderheader.OrderID = 1001;
This query uses ON orderheader.CustomerID = customer.CustomerID as the join predicate and WHERE orderheader.OrderID = 1001 as the filtering predicate. Table-qualified column names remove ambiguity because both tables contain CustomerID. section 8.2.1
Use predicates carefully when querying related tables:
- Put the table relationship in an ON condition, such as matching CustomerID values across orderheader and customer. section 8.2.1
- Use AND for additional filtering conditions in WHERE, so every required condition is evaluated. section 8.2.1
- Qualify shared column names with table.column, rather than writing an ambiguous comparison such as CustomerID = CustomerID. section 8.2.1
- Check predicate placement when updating through multiple tables, because UPDATE syntax differs among RDBMS products. section 17.1.4
Updating titles for one author
A multitable UPDATE can use a join predicate to connect title with titleauthor, then a WHERE predicate to limit the change to AuthorID 12. The statement updates title.Price only for titles linked to that author. Confirm the rows with a SELECT query first, and consult your RDBMS documentation because multitable UPDATE forms are not standardized. section 17.1.4
UPDATE title t
INNER JOIN titleauthor ta
ON t.TitleID = ta.TitleID
SET t.Price = 8.95
WHERE ta.AuthorID = 12;
The ON condition identifies title rows related to titleauthor rows; the WHERE condition selects the author whose titles receive the new price. section 17.1.4
