What you will be able to do
After this lesson you can read any columns you like from a table, do small calculations in a query, add comments, and put the parts of a query in the right order. Everything here uses the sample company database from the previous lesson.
The shape of a query
SELECT is how you ask a question of the data. It needs to know two things: which columns you want, and which table they live in.
SELECT * FROM departments;+----+-------------+-----------+
| id | name | city |
+----+-------------+-----------+
| 1 | Engineering | Pune |
| 2 | Sales | Mumbai |
| 3 | Marketing | Delhi |
| 4 | Finance | Mumbai |
| 5 | Support | Pune |
| 6 | Research | Bengaluru |
+----+-------------+-----------+The star means "every column". The table name follows FROM. This is a good first query when you meet a new table, because it shows you what is there.
A helpful way to read any query is to start at FROM. First decide which table is being read, then think about which rows, then finally which columns to display.
Choosing columns
Instead of the star, list the columns you want, separated by commas:
SELECT first_name, last_name, job_title FROM employees;+------------+-----------+----------------------+
| first_name | last_name | job_title |
+------------+-----------+----------------------+
| Meera | Iyer | Engineering Director |
| Arjun | Rao | Senior Developer |
| Kavya | Nair | Senior Developer |
| Rohan | Das | Developer |
| Isha | Kapoor | Developer |
| Vikram | Singh | Sales Director |
| Neha | Joshi | Account Executive |
| Sameer | Khan | Account Executive |
| Tara | Menon | Sales Associate |
| Dev | Patel | Marketing Manager |
| Anika | Shah | Content Strategist |
| Yash | Verma | Designer |
| Lata | Bose | Finance Manager |
| Omar | Sheikh | Accountant |
| Pooja | Reddy | Support Lead |
| Karan | Mehta | Support Engineer |
| Sana | Qureshi | Support Engineer |
| Ravi | Pillai | Consultant |
| Diya | Chopra | Junior Developer |
| Aman | Gupta | Sales Associate |
+------------+-----------+----------------------+You get exactly those three columns, in the order you wrote them, and one result row per row of the table. The table has 20 employees, so the result has 20 rows.
The order in your select list does not have to match the table. You can also repeat a column:
SELECT job_title, last_name, last_name FROM employees LIMIT 3;+----------------------+-----------+-----------+
| job_title | last_name | last_name |
+----------------------+-----------+-----------+
| Engineering Director | Iyer | Iyer |
| Senior Developer | Rao | Rao |
| Senior Developer | Nair | Nair |
+----------------------+-----------+-----------+The star is handy for exploring. For code that will keep running, prefer a named list. If someone adds a column to the table next month, a query written with the star silently starts returning more data than your program expects, and it also sends bytes you never use.
Expressions and literals
The select list is not limited to columns. It can contain calculations, which are worked out separately for each row:
SELECT first_name, salary, salary / 12 AS monthly_salary FROM employees LIMIT 4;+------------+--------+----------------+
| first_name | salary | monthly_salary |
+------------+--------+----------------+
| Meera | 180000 | 15000 |
| Arjun | 125000 | 10416 |
| Kavya | 125000 | 10416 |
| Rohan | 82000 | 6833 |
+------------+--------+----------------+The AS monthly_salary part names the new column. The next lesson explains these aliases properly. Look closely at the numbers, though: 180000 divided by 12 is exactly 15000, but 125000 divided by 12 gives 10416, not 10416.66. Here both numbers are integers, and SQLite, PostgreSQL and SQL Server divide integers as integers and drop the fraction. MySQL returns a decimal for the same query. To get a decimal everywhere, make one side a decimal number, for example salary / 12.0.
You can also select a fixed value. It appears on every row:
SELECT 'Employee' AS kind, first_name FROM employees LIMIT 3;+----------+------------+
| kind | first_name |
+----------+------------+
| Employee | Meera |
| Employee | Arjun |
| Employee | Kavya |
+----------+------------+Because a calculation or a literal needs no table, you can run a query with no FROM at all:
SELECT 2 + 3 AS five, 'hello' AS greeting;+------+----------+
| five | greeting |
+------+----------+
| 5 | hello |
+------+----------+MySQL, PostgreSQL, SQLite and SQL Server all accept a SELECT with no FROM. One notable database that does not is Oracle, which needs a dummy table.
Single quotes and double quotes
In standard SQL, single quotes mark text: 'Pune'. Double quotes mark a name, such as a column called "first_name". Mixing them up leads to puzzling results. For example, SELECT 'job_title' FROM employees returns the literal text on every row instead of reading the column:
SELECT 'job_title' FROM employees LIMIT 2;+-------------+
| 'job_title' |
+-------------+
| job_title |
| job_title |
+-------------+Some databases bend the rules: MySQL, by default, uses backticks for names and also accepts double-quoted text, and SQL Server also accepts square brackets around names. Staying with single quotes for text and plain, unquoted lower-case names keeps your SQL portable.
Comments
Comments are notes for humans, and the database ignores them. A line comment starts with two dashes and runs to the end of the line. A block comment sits between /* and */:
-- How many people work here?
SELECT COUNT(*) AS headcount /* counts every row */
FROM employees;+-----------+
| headcount |
+-----------+
| 20 |
+-----------+MySQL needs a space after the two dashes, so write -- comment, never --comment.
The order of clauses
A query can have more parts than SELECT and FROM. When you use them, they must come in this order:
SELECT what to show
FROM where it comes from
WHERE which rows to keep
GROUP BY how to bundle rows
HAVING which bundles to keep
ORDER BY how to sort
LIMIT how many rows to returnYou do not need to use all of them, and the next lessons add them one by one. Do not worry about GROUP BY and HAVING yet.
Note that the database does not carry them out in the written order. It first finds the table, then filters rows, and only then works out the select list and sorts. That is why a name you invent in SELECT cannot be used inside WHERE, which a later lesson explains in full.
Differences between databases
- Row limits.
LIMIT 3works on MySQL, PostgreSQL and SQLite. SQL Server writesSELECT TOP 3 .... The standard formFETCH FIRST 3 ROWS ONLYworks on PostgreSQL, and on SQL Server when it followsORDER BYand anOFFSETclause, but not on MySQL or SQLite. The limit lesson shows all of them. - Integer division differs, as shown above.
- Quoting names uses double quotes in the standard, backticks in MySQL and square brackets or double quotes in SQL Server.
The MySQL SELECT lesson goes deeper on that one product.
Common mistakes
- A trailing comma before
FROM, as inSELECT first_name, FROM employees, is a syntax error. - A missing comma between columns. In some databases
SELECT first_name last_name FROM employeesis read as the columnfirst_namerenamed tolast_name, so you get wrong data without an error. - Using the star in application code, which breaks when the table changes.
- Confusing quotes, so that text is read as a column or a column is read as text.
Exercise
Write a query that shows each employee's first_name, last_name and salary, but only the first five rows.
Show one possible answer
SELECT first_name, last_name, salary
FROM employees
LIMIT 5;+------------+-----------+--------+
| first_name | last_name | salary |
+------------+-----------+--------+
| Meera | Iyer | 180000 |
| Arjun | Rao | 125000 |
| Kavya | Nair | 125000 |
| Rohan | Das | 82000 |
| Isha | Kapoor | 78000 |
+------------+-----------+--------+On SQL Server, write SELECT TOP 5 first_name, last_name, salary FROM employees; instead.
Key points
- SELECT lists the columns you want and FROM names the table they come from. A query only reads, it never changes data.
- SELECT * returns every column. It suits exploring, but real code should name the columns it needs.
- A select list can hold expressions and literal values as well as column names.
- Clauses must be written in a fixed order: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, then the row limit.
- Text values use single quotes. Double quotes are for names, and some databases use other quote styles.