The SELECT statement is used to retrieve data from a table, allowing you to query one or more columns.

Basic Syntax

SELECT first_name, last_name
FROM person;

Select the first and last names from the person table.

To select all columns, use the * wildcard:

SELECT * FROM person;

Selecting Distinct Values

Use DISTINCT to return only unique values, eliminating duplicate rows:

SELECT DISTINCT last_name
FROM person;

Select all unique last names from the person table.

You can use DISTINCT with multiple columns:

SELECT DISTINCT first_name, last_name
FROM person;

Returns unique combinations of first and last names.

Filtering Results

Use the WHERE clause to filter rows based on conditions:

SELECT first_name, last_name
FROM person
WHERE person_id = 5;

Subqueries and Set Membership

IN Operator

The IN operator checks if a value matches any value in a list or subquery result:

SELECT first_name, last_name
FROM person
WHERE person_id IN (1, 2, 3);

Select persons with IDs 1, 2, or 3.

Using IN with a subquery:

SELECT name
FROM student
WHERE student_id IN (
    SELECT student_id
    FROM enrollment
    WHERE course_id = 101
);

Select students enrolled in course 101.

NOT IN Operator

The NOT IN operator checks if a value does not match any value in a list or subquery result:

SELECT first_name, last_name
FROM person
WHERE person_id NOT IN (1, 2, 3);

Select all persons except those with IDs 1, 2, or 3.

Using NOT IN with a subquery:

SELECT name
FROM student
WHERE student_id NOT IN (
    SELECT student_id
    FROM enrollment
);

Select students who are not enrolled in any course.

Nested Queries

Subqueries can be nested within other queries in various clauses:

In SELECT clause:

SELECT first_name, last_name,
    (SELECT COUNT(*) 
     FROM enrollment 
     WHERE enrollment.student_id = student.student_id) AS course_count
FROM student;

Select students with their enrollment count.

In WHERE clause with comparison operators:

SELECT name
FROM student
WHERE student_id = (
    SELECT student_id
    FROM enrollment
    WHERE course_id = 101
    LIMIT 1
);

Using EXISTS:

SELECT name
FROM student
WHERE EXISTS (
    SELECT 1
    FROM enrollment
    WHERE enrollment.student_id = student.student_id
    AND course_id = 101
);

Select students who are enrolled in course 101.

Using NOT EXISTS:

SELECT name
FROM student
WHERE NOT EXISTS (
    SELECT 1
    FROM enrollment
    WHERE enrollment.student_id = student.student_id
);

Select students who have no enrollments.

Set Comparison Operators

Set comparison operators allow you to compare a value against a Set of values returned by a subquery.

ANY Operator

The ANY operator compares a value to any value in a Set. The condition is true if the comparison is true for at least one value in the Set.

SELECT name, grade
FROM student
WHERE grade > ANY (
    SELECT grade
    FROM student
    WHERE course_id = 101
);

Select students whose grade is greater than at least one grade in course 101.

Common uses of ANY:

• = ANY is equivalent to IN
• > ANY means greater than the minimum value
• < ANY means less than the maximum value

ALL Operator

The ALL operator compares a value to all values in a Set. The condition is true only if the comparison is true for every value in the Set.

SELECT name, grade
FROM student
WHERE grade > ALL (
    SELECT grade
    FROM student
    WHERE course_id = 101
);

Select students whose grade is greater than all grades in course 101.

Common uses of ALL:

• != ALL (or <> ALL) is equivalent to NOT IN
• > ALL means greater than the maximum value
• < ALL means less than the minimum value

Example comparing ANY and ALL:

-- Students with salary higher than ANY engineer (at least one)
SELECT name, salary
FROM employee
WHERE salary > ANY (
    SELECT salary FROM employee WHERE department = 'Engineering'
);

-- Students with salary higher than ALL engineers (every one)
SELECT name, salary
FROM employee
WHERE salary > ALL (
    SELECT salary FROM employee WHERE department = 'Engineering'
);

Ordering Results

Use ORDER BY to sort results in ascending (ASC) or descending (DESC) order:

SELECT first_name, last_name
FROM person
ORDER BY last_name ASC, first_name ASC;

Limiting Results

Use LIMIT to restrict the number of rows returned:

SELECT first_name, last_name
FROM person
LIMIT 10;