--- title: 'SELECT Structure' source: 'https://academia.sh/en/courses/sql-fundamentals/select-structure' course: 'SQL Fundamentals' language: en updated: '2026-08-23T07:00:52+00:00' license: 'CC BY-SA 4.0' --- # SELECT Structure What the column list accepts, aliases, computed expressions, the elimination of duplicate rows, and the difference between the writing order and the evaluation order of clauses. The previous lesson separated SQL's five statement families and stated that the querying family consists of a single statement — `SELECT`. That statement was used once as an example: two columns, one table, one condition. Yet what can be written into the `SELECT` clause is not limited to the names of a table's columns. This lesson establishes what the column list accepts. The result a query produces is a new relation; its columns do not have to be the same as the table's columns. Their names can change, their number can change, and a column that does not exist at all can be added by computing it. The block below sets up the schema and sample data used throughout the course. Every query in this lesson runs against the `library.db` file created in this block. ```sh rm -f library.db sqlite3 library.db <<'SQL' CREATE TABLE branch (branch_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL); CREATE TABLE book (book_id INTEGER PRIMARY KEY, title TEXT NOT NULL, author TEXT NOT NULL, publication_year INTEGER, branch_id INTEGER REFERENCES branch(branch_id)); CREATE TABLE member (member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, email TEXT, registered_at TEXT NOT NULL); CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL REFERENCES book(book_id), member_id INTEGER NOT NULL REFERENCES member(member_id), pickup_date TEXT NOT NULL, return_date TEXT); INSERT INTO branch VALUES (1,'Central','Ankara'),(2,'Bahcelievler','Ankara'), (3,'Kadikoy','Istanbul'),(4,'Konak','Izmir'); INSERT INTO book VALUES (1,'Blindness','José Saramago',1995,1),(2,'The Disconnected','Oğuz Atay',1972,1), (3,'The Book of Sand','Jorge Luis Borges',1975,2),(4,'Yaban','Yakup Kadri',1932,2), (5,'Silent House','Orhan Pamuk',1983,3),(6,'Motherland Hotel','Yusuf Atılgan',NULL,3), (7,'Tehlikeli Oyunlar','Oğuz Atay',1973,NULL); INSERT INTO member VALUES (1,'Alice','Kane','alice@example.test','2023-02-14'), (2,'Ben','Ortiz','ben@example.test','2023-05-30'),(3,'Clara','Diaz',NULL,'2024-01-09'), (4,'Derek','Voss','derek@example.test','2024-03-22'),(5,'Grace','Kim',NULL,'2024-11-05'), (6,'Owen','Park','owen@example.test','2025-01-18'); INSERT INTO loan VALUES (1,1,1,'2025-01-10','2025-01-24'),(2,2,1,'2025-02-02','2025-02-20'), (3,1,2,'2025-02-11',NULL),(4,3,3,'2025-03-01','2025-03-15'),(5,4,3,'2025-03-18','2025-04-02'), (6,1,4,'2025-04-05','2025-04-19'),(7,5,4,'2025-04-21',NULL),(8,2,5,'2025-05-02','2025-05-30'), (9,7,1,'2025-05-14','2025-05-28'),(10,3,5,'2025-06-03',NULL),(11,6,2,'2025-06-11','2025-06-25'), (12,4,4,'2025-06-20','2025-07-04'); SQL ``` ## Star and Explicit List The shortest query asks for all of the table's columns. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT * FROM branch; SQL ``` ``` branch_id name city --------- ------------ -------- 1 Central Ankara 2 Bahcelievler Ankara 3 Kadikoy Istanbul 4 Konak Izmir ``` The star means "all of the table's columns as they currently stand." It is useful during exploration and causes three separate problems in code meant to last. First, when a column is added to the schema later, the result set's column count changes silently; a caller that reads columns by position breaks. Second, columns that are not needed get read too — for a wide text column, this means unnecessary disk and network traffic. Third, the person reading the query cannot see which columns are actually needed. An explicit list removes all three problems at once. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT title, author FROM book; SQL ``` ``` title author ----------------- ----------------- Blindness José Saramago The Disconnected Oğuz Atay The Book of Sand Jorge Luis Borges Yaban Yakup Kadri Silent House Orhan Pamuk Motherland Hotel Yusuf Atılgan Tehlikeli Oyunlar Oğuz Atay ``` The order in the column list is the order in the result. The table's physical order is not binding. ## Aliases A column's name in the result set can be replaced with `AS`. This is called an **alias**. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT title AS book_title, author AS written_by FROM book WHERE branch_id = 1; SQL ``` ``` book_title written_by ---------------- ------------- Blindness José Saramago The Disconnected Oğuz Atay ``` An alias does two jobs. It gives the column a meaningful name — especially for computed expressions, since a computed column's default name is a string that changes from engine to engine. Second, it lets two columns carrying the same name be told apart in joins; that is the second topic's subject. The word `AS` is optional in most engines — writing `title book_title` also works. Still, writing `AS` prevents a forgotten comma in the column list from silently turning into an alias. The statement `SELECT title author FROM book` does not raise an error; instead of two columns, it returns a single column named `author`. ## Computed Expressions The column list can hold an **expression** instead of a column name. The expression is evaluated per row and appears in the result set as a new column. Text concatenation is written with the `||` operator in the standard. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT first_name || ' ' || last_name AS full_name, registered_at FROM member; SQL ``` ``` full_name registered_at ---------- ------------- Alice Kane 2023-02-14 Ben Ortiz 2023-05-30 Clara Diaz 2024-01-09 Derek Voss 2024-03-22 Grace Kim 2024-11-05 Owen Park 2025-01-18 ``` The syntax of the concatenation operator changes from engine to engine: some engines offer a concatenation function instead of `||` or alongside it, and in some, `||` carries an entirely different meaning. In queries that need to be portable, this is one of the first things to check. Arithmetic expressions are written the same way. The query below computes the decade from the publication year. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT title, publication_year, (publication_year / 10) * 10 AS decade FROM book; SQL ``` ``` title publication_year decade ----------------- ---------------- ------ Blindness 1995 1990 The Disconnected 1972 1970 The Book of Sand 1975 1970 Yaban 1932 1930 Silent House 1983 1980 Motherland Hotel Tehlikeli Oyunlar 1973 1970 ``` There are two observations. First, whether the division of two integers is evaluated as an integer or as a decimal depends on the engine; because integer division applies here, `1995 / 10` produced `199`. The portable way to write it is to state the type explicitly before dividing. Second, the `decade` column is blank in the `Motherland Hotel` row. Because the publication year is unknown, the expression's result is unknown too: an arithmetic expression that includes a null value produces a null value. This behavior has enough consequences to fill the entire fifth lesson. ## Eliminating Duplicate Rows A `SELECT` result is a multiset: the same row can appear more than once. The `DISTINCT` keyword eliminates duplicate rows. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT DISTINCT author FROM book; SQL ``` ``` author ----------------- José Saramago Oğuz Atay Jorge Luis Borges Yakup Kadri Orhan Pamuk Yusuf Atılgan ``` There are seven books, but six authors came back: the same author's two books collapsed into a single row. `DISTINCT` applies not to a column, but to the entire result row. The syntax `SELECT DISTINCT author, title` does not deduplicate authors; it deduplicates author–title pairs, and because the book titles differ, no row gets eliminated. This is the most common mistake made with `DISTINCT`: wanting to deduplicate one column, but adding a second column to the result set leaves the elimination without effect. The output order should not be relied on. The row order of a query with no sort specified is undefined; the order comes out according to whichever method the engine uses to deduplicate. When order is required, the sorting clause from the fourth lesson is written. ## Evaluation Order The order they are written in is `SELECT`, `FROM`, `WHERE`; the order they are evaluated in is `FROM`, `WHERE`, `SELECT`. The engine first decides which table to read from, then filters the rows, and computes the column list last. This has a measurable consequence: an alias defined in the `SELECT` clause is not visible, according to the standard, in the `WHERE` clause that is evaluated before it. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT first_name || ' ' || last_name AS full_name FROM member WHERE full_name = 'Alice Kane'; SQL ``` ``` full_name ---------- Alice Kane ``` The query ran here. This is not standard behavior, though — it is a relaxation the engine in use happens to provide; the same query can raise a "no such column" error on another engine. The behavior depends on the engine. The portable way to write it repeats the expression inside `WHERE`: ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT first_name || ' ' || last_name AS full_name FROM member WHERE first_name || ' ' || last_name = 'Alice Kane'; SQL ``` ``` full_name ---------- Alice Kane ``` Writing the expression twice does not mean the engine computes it twice; in a declarative language, repetition belongs to the syntax, not to the plan. ## Summary - The result set is a new relation; its columns do not have to be the same as the table's columns. - Star syntax is convenient for exploration; it is fragile to schema changes and reads unnecessary data in queries meant to last. - An alias names a column; writing `AS` prevents a forgotten comma from silently turning into an alias. - The column list can hold an expression; an expression that includes a null value produces a null value. - `DISTINCT` applies to the entire result row, not to a single column. - The writing order and the evaluation order are separate; whether an alias defined in `SELECT` is visible in `WHERE` depends on the engine. ## Next Step The `WHERE` clauses in this lesson consisted of nothing but a single equality. Yet filtering is the clause that determines a query's result the most: comparison operators, logical connectives, range and membership checks, text pattern matching. The next lesson builds condition syntax in all its forms and shows why the case-sensitivity behavior of pattern matching depends on the engine.