IB Computer Science Topic A3.3.2: Constructing Queries Between Two Tables in SQL Notes

A Join Combines Two Tables by Matching Values in a Shared Column

1. A normalized database splits related facts across tables, so an artwork sits in Item while the person who made it sits in Artist.
2. The JOIN clause puts them back together by matching the foreign key in one table to the primary key in the other.
3. Which rows survive the match depends on the type of join you choose.
4. Inner Join
   1. Returns only the rows that have a matching value in both tables.
   2. An item with no artist recorded is dropped from the result entirely.
5. Left Join
   1. Returns every row from the left table plus any matching rows from the right table.
   2. Where there is no match, the right table's columns come back as NULL.
6. Right Join
   1. Returns every row from the right table plus any matching rows from the left table.
   2. It is a left join with the tables written the other way round, so the same result can be produced either way.

SELECT Chooses the Columns and DISTINCT Removes Repeated Rows

1. SELECT lists the columns you want back, and FROM names the table they come from.
2. SELECT * returns every column, which is convenient when exploring data but wasteful in a real query.
3. DISTINCT removes duplicate rows from the result, so each combination of the selected values appears once.
4. A join often produces duplicates: an artist with four items in the gallery appears four times unless you ask for distinct names.

Relational Operators Are the Comparisons Inside a Condition

1. A relational operator compares two values and gives back true or false, which is what WHERE and ON need to decide whether to keep a row.
2. They work on numbers, on dates and on text, where the ordering is alphabetical.

WHERE, BETWEEN and LIKE Narrow the Rows Down

1. WHERE keeps only the rows whose condition is true, and it is applied before any grouping happens.
2. BETWEEN tests a range and is inclusive at both ends.
   1. ItemCost BETWEEN 100 AND 300 is the same as ItemCost >= 100 AND ItemCost <= 300.
3. LIKE matches a text pattern rather than an exact value, using the % wildcard to stand for any run of characters, including none.
   1. 'Cer%' matches names starting with Cer, '%ics' matches names ending in ics.
   2. '%Ceramics%' matches the word anywhere in the value, which is how a search box is usually built.

AND, OR and NOT Build Conditions Out of Simpler Ones

1. AND keeps a row only when every condition it joins is true.
2. OR keeps a row when at least one of the conditions is true.
3. NOT reverses the condition that follows it.
4. AND is evaluated before OR, so brackets are needed whenever you want the OR tested first.

ORDER BY Sorts the Output While GROUP BY Collapses It

1. ORDER BY sorts the rows that are returned, using ASC for ascending or DESC for descending.
   1. Ascending is the default, so a column with no keyword after it is sorted smallest first.
2. GROUP BY collapses all the rows that share a value into a single row, so eight items by one artist become one row for that artist.
3. HAVING filters those grouped rows, testing a condition on the group rather than on an individual row.

Grouping in Practice: Counting Items per Artist

1. Grouped rows are usually reported with a function such as COUNT, which returns how many rows fell into each group.
2. The full set of aggregate functions is examined at HL, and A3.3.4 covers AVERAGE, COUNT, MAX, MIN and SUM in detail.

The Order the Database Applies the Clauses

1. FROM and JOIN run first, building one combined set of rows out of the two tables.
2. WHERE then throws away the rows that fail its condition.
3. GROUP BY collapses what is left into groups.
4. HAVING discards whole groups that fail its condition.
5. SELECT picks the columns, DISTINCT removes repeats, and ORDER BY sorts the finished result last.
6. Knowing this order explains why WHERE cannot test a count and why ORDER BY can sort by a column alias that SELECT created.

A Worked Query That Uses Every Clause

1. A gallery stores customers in Customer and their orders in Purchase, linked by CustomerID.
2. The manager wants each Italian or French customer who bought during the last week of July 2025, with the busiest buyer first.
3. That requires a join for the two tables, a bracketed OR for the countries, BETWEEN for the dates, a group per customer and a descending sort.

Why a Two-Table Query Returns the Wrong Rows

1. The ON condition is missing
   1. Every row of one table is paired with every row of the other, so 200 items and 50 artists return 10,000 rows.
2. The join is on the wrong pair of columns
   1. Match the foreign key to the primary key it refers to, not two columns that happen to share a name.
3. Rows appear more times than expected
   1. One artist with four items produces four rows, which is correct behaviour rather than a fault.
   2. Use DISTINCT if you wanted the artist once, or GROUP BY if you wanted a figure per artist.
4. An inner join has silently dropped rows
   1. Items with no artist recorded vanish, so use a left join when the unmatched rows still matter.