Unit 3 · Lesson 114 minAcademic review pending

SELECT is the algebra you learned, typed as one sentence

SQL's basic query form is selection, projection, and join wearing a readable syntax. This lesson connects the SELECT-FROM-WHERE shape directly to Unit 2's algebra, then adds the three set operations that combine full query results.

Choose explanation

After this lesson

You should be able to

Map SELECT, FROM, and WHERE onto projection, the source relation, and selection.
Use UNION, INTERSECT, and EXCEPT correctly on two compatible query results.

SELECT-FROM-WHERE is projection, source, and selection in one line

SELECT name, branch FROM Student WHERE branch = 'CSE' reads left to right as three separate algebra ideas glued together: FROM Student names the source relation, WHERE branch = 'CSE' is exactly Unit 2's selection (sigma), keeping only matching rows, and SELECT name, branch is exactly projection (pi), keeping only those columns. Confusingly, SQL's SELECT keyword does the job of algebra's projection, not algebra's selection — a naming clash worth remembering deliberately.

A join across two relations appears as a second table in FROM plus a matching condition in WHERE — SELECT b.title FROM Book b, Borrows r WHERE b.isbn = r.isbn AND r.membership_id = 'M101' is the same select-join-project chain from the previous unit's practice problem, now written as real, runnable syntax.

Selection, source, and join, all inside one WHERE clause

SELECT b.title
FROM Book b, Borrows r
WHERE b.isbn = r.isbn
  AND r.membership_id = 'M101';

Combining whole query results: UNION, INTERSECT, EXCEPT

These three map directly onto Unit 2's set operations, applied to two SELECT results instead of two raw relations. UNION combines rows from two queries, automatically removing duplicates — students in CSE UNION students on the merit list gives every student in either group, once each. INTERSECT keeps only rows appearing in both results — students in CSE INTERSECT students on the merit list gives CSE students who are also on the merit list.

EXCEPT keeps rows from the first result that do not appear in the second — students in CSE EXCEPT students who already paid their fee gives exactly the CSE students who still owe money. All three require both queries to return the same number of columns with compatible types, the same rule that governed algebra's set operations in Unit 2.

Try it yourself

Write a query that finds students who are in the CSE branch but do not appear in a Scholarship table, using Student(roll_number, name, branch) and Scholarship(roll_number, amount).

Need a hint?

This is asking for rows in one set but not the other — exactly what EXCEPT is for. Select roll numbers from CSE students, then EXCEPT the roll numbers already in Scholarship.

Check the worked solution

SELECT roll_number FROM Student WHERE branch = 'CSE' EXCEPT SELECT roll_number FROM Scholarship. The first query gets every CSE student's roll number; EXCEPT then removes any roll number that already appears in Scholarship, leaving exactly the CSE students with no scholarship on record.

Quick check

In SQL, why does the keyword SELECT correspond to relational algebra's projection rather than its selection?

Select an answer to check your thinking.

Why this lesson exists

Syllabus mapping

Basic SQL Query Form, UNION, INTERSECT, and EXCEPT

Maps to course outcomes CO1, CO2.