SELECT Structure

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.

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','[email protected]','2023-02-14'),
  (2,'Ben','Ortiz','[email protected]','2023-05-30'),(3,'Clara','Diaz',NULL,'2024-01-09'),
  (4,'Derek','Voss','[email protected]','2024-03-22'),(5,'Grace','Kim',NULL,'2024-11-05'),
  (6,'Owen','Park','[email protected]','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.

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.

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.

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.

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.

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.

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.

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:

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.