SQL SELECT: Querying a Database

A query asks a database for data. A plain SELECT query reads the stored table without changing it. SELECT names the columns to display, and FROM names the table to read.

The text examples use ordinary case-sensitive equality in SQLite and PostgreSQL. Collation settings can change how text compares and sorts.

Two tables to work with

The employees table has these eight rows. salary is yearly pay in dollars; hire_date is text in YYYY-MM-DD form. NULL marks a missing value.

The products table records prices in dollars and stock in warehouse units.

Read the query's shape

SELECT city, first_name
FROM employees;

The result set is another table: eight rows and two columns, with city first. No filter is present, so every employee contributes a row. SQL gives no guaranteed row order until you add ORDER BY.

SELECT * FROM products; displays every column in the table's defined order. The star is useful for exploring. In a saved query, naming the columns keeps the result focused and prevents a newly added table column from changing its shape.

Make the syntax readable

Separate columns with commas, with no trailing comma before FROM. End the statement with a semicolon. Keywords ignore case, so select works, but capitals make clauses easy to find. Spaces and line breaks separate words; they do not change this query's meaning. A -- comment continues to the end of its line.

Names must resolve to the schema. If frist_name causes a missing-column error, compare the spelling with first_name. Standard SQL uses double quotes for names containing spaces or reserved words, such as "order date". PostgreSQL folds unquoted names to lowercase, while "City" asks for that exact capitalized name. MySQL normally uses backticks; double quotes identify names in its ANSI_QUOTES mode. SQL Server also accepts square brackets and accepts double quotes when QUOTED_IDENTIFIER is on, its default.

A repeated value in a query result is often useful. Several employees can live in the same city, and each employee still needs a row. Use DISTINCT when the question asks for different values or different combinations.

Choose what should count as a duplicate

SELECT DISTINCT city
FROM employees;

The result contains Chicago, Denver, and Boston, with no guaranteed order. The plain SELECT city would return eight rows because each employee supplies a city. Adding DISTINCT reduces that display to three different values. It leaves all eight employee records in storage.

The comparison includes every selected column. Consider:

SELECT DISTINCT department, city
FROM employees;

Ana and Chloe both contribute (Sales, Chicago), so that pair appears once. Emma contributes (Sales, Denver), which is different and stays. The other pairs are (IT, Denver), (Marketing, Boston), (IT, Chicago), (NULL, Boston), and (Marketing, Chicago). There are seven result rows. Repeated departments remain because a department paired with another city is a different result row.

Write DISTINCT once, directly after SELECT. It applies to the complete selected row. Placing it between column names, as in SELECT department, DISTINCT city, is a syntax error.

Watch which columns you include

If you add employee_id to the selected columns, every row becomes different. SELECT DISTINCT employee_id, city FROM employees; still returns eight rows: each ID is unique, even where the city repeats. To answer “Which cities have employees?”, select the city alone. To answer “Which employees live in each city?”, keep identifying columns and expect repeated cities.

For this duplicate-removal step, repeated NULLs are treated as duplicates. SELECT DISTINCT department FROM employees; returns Sales, IT, Marketing, and one NULL row. The NULL row records that a department is missing; it is not a fourth department name. This duplicate rule differs from NULL = NULL, which gives unknown in a filter.

Text comparisons follow a collation, the database's rules for matching and ordering text. With the ordinary case-sensitive comparisons used for these SQLite and PostgreSQL examples, Chicago and chicago are different values. A DISTINCT list can expose inconsistent capitalization. Removing repeated displays does not repair spelling in the stored data.

Combine DISTINCT with a sorted shortlist

SELECT DISTINCT city
FROM employees
ORDER BY city
LIMIT 2;

First, duplicate cities disappear. Next, the three cities are sorted. Finally, two result rows remain: Boston and Chicago, in that order. Applying the limit to employee rows first would answer a different question.

For a DISTINCT query, keep the sort columns in the SELECT list. PostgreSQL requires this; SQLite permits some queries that sort on an unselected column, but choosing among different hidden values makes their ordering hard to interpret. If a result unexpectedly repeats, inspect which columns you selected before deciding that DISTINCT belongs in the query.

The WHERE clause keeps a row only when its condition is true. It follows FROM and can test any column in the table, including a column you do not display.

Match the words to the operator

<> is standard SQL for “not equal.” The five databases discussed here also accept !=. Use one equals sign for equality. SQLite accepts ==, but PostgreSQL rejects that form.

Trace a boundary row

SELECT first_name, salary
FROM employees
WHERE salary > 60000;

Ben has 71000, Emma has 65000, and Farid has 83000. Those are the three qualifying rows, with no stated order. David earns exactly 60000. His row fails > 60000 because “more than” excludes equality.

Change only the operator to >=. David now joins the result, giving four rows. This is how “at least 60000” differs from “more than 60000.” A useful boundary check asks what happens to a value exactly equal to the threshold; values far above it cannot reveal that mistake.

For “at most 20.50,” a price of 20.50 passes and a price of 20.51 fails. When a request gives both a lower and an upper bound, check each end separately before joining the tests.

Write numeric values as numbers

Use plain digits in SQL conditions: 60000, with no thousands comma or currency sign. A comma separates SQL items; WHERE salary > 60,000 is a syntax error. Decimal values use a point, as in WHERE price < 20.50. Salaries and prices belong in numeric columns so that comparisons use their numeric size.

Text that looks numeric follows text rules when compared as text. The text value '9' sorts after '10' because its first character comes later. That tells you about characters, not about nine being greater than ten. Read the column's data type before interpreting an unexpected comparison.

Filter without displaying the tested column

SELECT first_name
FROM employees
WHERE salary < 50000;

Chloe and Grace pass. Their salaries are used to test the rows but are absent from the result. You do not need to add salary to SELECT to make the filter work. While checking a query, showing it temporarily can help you inspect the reason each row passed.

Equality on a primary key provides a direct record lookup: WHERE employee_id = 6 finds Farid. A primary key identifies one row, so that equality cannot return several employees with the same ID.

Missing data needs separate care. WHERE stock <> 0 returns six products. Lamp Shade is left out because its known stock is zero. Bookshelf is also left out: its stock is NULL, so the comparison is unknown and WHERE does not keep it. A “not equal” test does not add missing values to its result.

Text and date conditions need values in a form the database can interpret. A misspelled value may quietly return no rows, while a missing quote can turn that value into a column name.

Distinguish a value from a name

SELECT first_name
FROM employees
WHERE city = 'Chicago';

The single quotes make 'Chicago' a text value. The result contains Ana, Chloe, Farid, and Hugo. Without quotes, Chicago is read as a name, so the database looks for a column with that name. Standard double quotes also mark names. PostgreSQL rejects city = "Chicago" when no such column exists. Some SQLite configurations accept double-quoted strings as a legacy feature; use single quotes for values consistently.

An apostrophe inside a value is doubled:

SELECT first_name
FROM employees
WHERE last_name = 'O''Neill';

That finds Hugo. The two adjacent single quotes represent one apostrophe inside the value; the outer quotes mark its start and end. Standard SQL does not use a backslash to escape that apostrophe.

Check the comparison rules

A collation controls text equality and sorting, including case and accented letters. In the ordinary case-sensitive SQLite and PostgreSQL comparisons used here, 'Chicago' and 'chicago' differ. WHERE city = 'chicago' returns no rows. MySQL and SQL Server commonly use case-insensitive collations, so their usual settings can match both spellings.

Text range comparisons also follow collation. With these names, WHERE last_name < 'M' returns Brown, Carter, Haddad, Kim, and Lopez. Values are compared from the left. A longer value beginning with the same characters comes after its prefix: 'Martin' is after 'M'. A cutoff of 'M' is not the same as “all names beginning with M.”

Put the year first

The example hire_date column stores full dates as text: year, month, then day, with fixed widths. This makes text order agree with calendar order.

SELECT first_name, hire_date
FROM employees
WHERE hire_date >= '2022-01-01';

Chloe, Grace, and Hugo pass. PostgreSQL also has a real DATE type that accepts this literal. Its standard typed spelling is DATE '2022-01-01'; SQLite does not use that typed-literal syntax.

A local date string such as '03/04/2024' is ambiguous in a date column because settings can make it March 4 or April 3. Stored as text, month-first dates compare by month before year. For example, '12/01/2021' sorts after '03/15/2024' even though the first calendar date is earlier.

Translate the full date boundary

“Hired after 2022” means starting with '2023-01-01'. Comparing text to '2022' instead also admits Hugo's '2022-08-05': the longer text sorts after the matching prefix.

If a column contains times, equality with a date alone does not select every time on that day. To include March 31, 2024, use ordered_at >= '2024-03-31' AND ordered_at < '2024-04-01'. This keeps every time from the start of March 31 up to, but excluding, the start of April 1.

A row may need to meet several tests. AND requires both conditions to be true. OR requires at least one to be true. NOT reverses true and false; when a condition is unknown because of NULL, NOT leaves it unknown.

Decide whether both tests must hold

SELECT first_name
FROM employees
WHERE department = 'Sales' AND city = 'Chicago';

Ana and Chloe pass both tests. Emma works in Sales but lives in Denver, so she fails this AND. Changing AND to OR admits Emma, Farid, and Hugo as well: the five qualifying employees satisfy one test or both.

Adding an AND condition can keep or reduce the result. Adding an OR condition can keep or expand it. This gives you a quick check when building a filter in stages.

Every comparison must be complete. Write department = 'IT' OR department = 'Marketing', repeating the column and operator. department = 'IT' OR 'Marketing' does not express the second comparison. PostgreSQL rejects it; SQLite treats the text on its own as false and returns only IT staff. Likewise, write salary > 50000 AND salary < 70000, with salary on both sides of AND.

Translate groups of alternatives

“Employees in Boston and Denver” usually asks for people in either city. Each row has one city, so use city = 'Boston' OR city = 'Denver'. That returns Ben, David, Emma, and Grace. An AND between these equalities asks one city value to be both places at once and returns no rows.

When operators mix, NOT is evaluated before AND, and AND before OR. Without parentheses:

WHERE department = 'Sales'
   OR department = 'Marketing' AND city = 'Chicago'

This means Sales staff anywhere, plus Marketing staff in Chicago. Emma passes because the Sales comparison is enough. To restrict both departments to Chicago, group the alternatives:

SELECT first_name
FROM employees
WHERE (department = 'Sales' OR department = 'Marketing')
  AND city = 'Chicago';

Now Ana, Chloe, and Hugo pass. The parentheses put the two department choices under one shared city requirement. Use them to make a mixed AND/OR filter clear even when the default order would already be right.

Reverse a whole group

NOT (city = 'Chicago' OR city = 'Denver') returns David and Grace in Boston. Neither listed city is allowed. It is equivalent to city <> 'Chicago' AND city <> 'Denver' for these values.

Using OR between those two “not equal” tests is a common trap. A Chicago row differs from Denver, and a Denver row differs from Chicago. Every employee with a recorded city passes at least one test, so the OR fails to exclude either city.

Build a complex condition one piece at a time. Check the rows after the first test, then after adding the next. You can swap the two sides of an AND, or the two sides of an OR, without changing the answer; choose the arrangement that makes the groups easiest to read.

Use IN when one column may match any value in a list. It shortens a group of equalities joined with OR while keeping their meaning.

Build a list of alternatives

SELECT first_name
FROM employees
WHERE department IN ('IT', 'Marketing');

Ben, David, Farid, and Hugo pass. Each has one of the two department values. The same test could be written as department = 'IT' OR department = 'Marketing'.

Put list items inside parentheses, separated by commas. Quote text; leave numeric values unquoted. For example, employee_id IN (2, 4, 6) selects Ben, David, and Farid. The order of list entries does not sort the result. Repeating an entry does not repeat a matching row: IN (6, 2, 2, 4) selects the same records. Use ORDER BY if you need a result order.

IN matches whole values under the same comparison rules as equality. city IN ('Chi') does not match Chicago. Under the ordinary case-sensitive comparisons used here, city IN ('chicago') does not match it either. A fragment search needs LIKE, while case behavior depends on collation.

Apply another rule to every choice

SELECT first_name
FROM employees
WHERE department IN ('IT', 'Marketing')
  AND city = 'Chicago';

Farid and Hugo pass. IN is one condition, so AND applies the city test to the entire department choice. In the expanded OR version, put parentheses around the two department comparisons. Without them, department = 'IT' OR department = 'Marketing' AND city = 'Chicago' also admits Ben, because AND binds more tightly and the city rule applies only to Marketing.

Exclude the listed values

WHERE city NOT IN ('Chicago', 'Denver') returns David and Grace. A recorded city passes only when it differs from both entries. This is a useful form for several exclusions and avoids the mistake of joining “not equal” conditions with OR.

Missing values are not a match for either form. Grace has a NULL department, so department NOT IN ('Sales') does not include her. The database cannot establish that the missing department differs from Sales. To include unassigned staff as well, write department NOT IN ('Sales') OR department IS NULL.

A NULL inside a NOT IN list creates a broader problem. department NOT IN ('Sales', NULL) requires both department <> 'Sales' and department <> NULL. The second comparison is always unknown, so the combined test is never true and WHERE returns no rows. Keep missing-value tests separate from the value list.

With IN, a real match still succeeds even if another list entry is NULL. department IN ('Sales', NULL) keeps the three Sales staff, but it does not add Grace. Use IS NULL to request missing values explicitly.

An ordinary SQL IN list needs at least one item. PostgreSQL rejects IN (); SQLite accepts it as false. If a program builds a list from user choices, handle an empty list before sending the query so the behavior is deliberate across databases.

BETWEEN tests a range with both ends included. Use it when “from” and “to” include the named boundary values; use explicit comparisons when either boundary must be excluded.

Check the two ends

SELECT name, price
FROM products
WHERE price BETWEEN 15.50 AND 89.00;

Desk Lamp, Floor Lamp, Lamp Shade, and Desk Organizer pass. Floor Lamp costs exactly 89.00 and Lamp Shade exactly 15.50, so both remain. The condition means price >= 15.50 AND price <= 89.00.

Put the lower value first. price BETWEEN 89.00 AND 15.50 asks for a price at least 89.00 and at most 15.50. It is valid syntax but has no matches. Ordinary BETWEEN does not reorder the endpoints for you. PostgreSQL offers BETWEEN SYMMETRIC to do that, but use the lower-first form for a query shared with SQLite.

For “more than 15.50 and less than 89.00,” write price > 15.50 AND price < 89.00. Only Desk Lamp and Desk Organizer pass. That difference is about the question's endpoints, not the way the result is displayed.

Outside a range

price NOT BETWEEN 20 AND 100 means price < 20 OR price > 100. It returns Lamp Shade, Office Chair, Standing Desk, Desk Organizer, Bookshelf, and Paper Tray. Values exactly 20 or 100 would be inside the original range and would fail NOT BETWEEN.

A missing tested value is unknown in either test. For stock, BETWEEN 5 AND 40 selects five products and NOT BETWEEN selects two: Lamp Shade with 0 and Desk Organizer with 60. Bookshelf has NULL stock and belongs to neither result. A range and its negation therefore need not cover every stored row.

Dates and times

Complete date strings in YYYY-MM-DD order support date ranges. hire_date BETWEEN '2020-01-01' AND '2022-12-31' selects Ben, David, and Hugo, covering all three calendar years in this date-only column.

A column containing times needs a different upper boundary for a whole month. A date-only endpoint in a timestamp column means midnight at the start of that day. In text, even '2024-03-31 00:00:00' sorts after the shorter prefix '2024-03-31'. To include all March times, use:

WHERE ordered_at >= '2024-03-01'
  AND ordered_at < '2024-04-01'

This includes the beginning of March and stops just before April, without guessing the final fraction of a second on March 31.

Text ranges and the two uses of AND

Text endpoints are complete values. last_name BETWEEN 'A' AND 'C' includes Brown but excludes Carter, since Carter sorts after its prefix C. Under these text rules, last_name >= 'A' AND last_name < 'D' covers names beginning A, B, or C and returns Brown and Carter.

In price BETWEEN 10 AND 100 AND category = 'Lighting', the first AND separates range endpoints; the second joins the range test to the category test. The matching products are Desk Lamp, Floor Lamp, and Lamp Shade. Line breaks can make the two roles easier to see.

LIKE tests text against a pattern. Use it to search for a beginning, an ending, or a fragment when equality would ask for a whole exact value.

Place the wildcards

The pattern must match the whole value. % can represent zero or more characters, including spaces. In '%Lamp%', either percent sign may match nothing, so the text can begin or end with Lamp as well as contain it in the middle.

_ stands for exactly one character. last_name LIKE '_im' finds Kim. Four underscores in first_name LIKE '____' find Emma and Hugo, the first names with exactly four letters. The underscore is not an optional character.

SELECT first_name, phone
FROM employees
WHERE phone LIKE '312-%';

Ana, Chloe, and Farid pass because their recorded phone text starts with 312-. The percent sign covers every remaining character.

Choose the operator and case rule

Wildcards belong to LIKE patterns. name = '%Lamp%' looks for literal percent signs and returns no products here. The file-search wildcards * and ? have no special meaning in LIKE; name LIKE 'Desk*' asks for the literal asterisk after Desk.

SQLite's LIKE normally ignores case for ASCII letters A–Z, while PostgreSQL's ordinary LIKE is case-sensitive. name LIKE '%desk%' therefore finds Desk Lamp, Standing Desk, and Desk Organizer in SQLite, but none in PostgreSQL under case-sensitive collation. PostgreSQL also provides the case-insensitive ILIKE. MySQL's behavior follows collation.

For the ASCII product names used here, LOWER(name) LIKE '%desk%' gives the same matches in SQLite and PostgreSQL: LOWER converts the name to lowercase before matching. Case conversion for other writing systems depends on database and language rules. SQLite's equality is also different from its LIKE: city = 'chicago' fails to find Chicago under the default binary collation even though city LIKE 'chicago' finds it.

Search for a real wildcard character

To match an underscore literally, choose an escape character:

WHERE code LIKE 'SPRING!_%' ESCAPE '!'

In a discount-code table, this matches 'SPRING_10' but not 'SPRINGS10'. The !_ is a real underscore; the last % still matches a sequence. Use the same approach with !% for a literal percent sign. Writing ESCAPE explicitly avoids relying on database defaults; PostgreSQL normally uses a backslash and SQLite has no default escape character.

Negation and search size

name NOT LIKE '%Lamp%' returns the other five products. Missing text passes neither LIKE nor NOT LIKE. Of the employees, three phones match '312-%', two recorded phones do not, and the three NULL phones appear in neither result.

A leading % gives an ordinary index no fixed starting text to narrow down, so a large part of the table may be scanned. A fixed start such as 'Desk%' can help an eligible index. For dates, use date ranges: LIKE on these text dates works, but PostgreSQL requires conversion before applying LIKE to a true DATE column.

NULL marks a missing value: a value may be unknown, not recorded, or inapplicable. It is different from a known zero or known text with no characters.

Read what a missing value means

Lamp Shade has stock = 0. Its recorded quantity is zero, so it is out of stock. Bookshelf has stock NULL: the quantity was not recorded. The database cannot tell you that it has zero units or any other number.

The distinction changes the question you can answer:

A blank-looking cell in a query tool is not enough to distinguish these states. Some tools show NULL as an empty cell; others print NULL. Test the stored value directly.

Use the missing-value tests

SELECT first_name
FROM employees
WHERE phone IS NULL;

Ben, Emma, and Hugo have no recorded phone. IS NULL gives true for their missing values. To find the five employees with a recorded phone, use phone IS NOT NULL: Ana, Chloe, David, Farid, and Grace.

Do not write phone = NULL to find missing phones. An ordinary comparison involving NULL gives unknown, even when both sides are NULL. WHERE keeps true rows only, so = NULL returns none. <> NULL has the same problem. IS NULL is a test for the missing marker and gives a definite true or false answer for these single-column values.

The form NOT phone IS NULL also finds present phones in SQLite and PostgreSQL, but phone IS NOT NULL makes the intended test easier to read.

Separate three kinds of text

SQLite, PostgreSQL, MySQL, and SQL Server distinguish empty text from NULL. phone = '' would find empty strings, not Ben's, Emma's, or Hugo's missing phones. If a column can contain both, use phone IS NULL OR phone = '' to find either state. Oracle treats an empty text value as NULL, so this distinction changes when you move databases.

Apply the wording and the schema

“Missing,” “not recorded,” and “not assigned” point to IS NULL. department IS NULL finds Grace. Her missing department does not mean the department text is the word 'NULL', nor does it establish which department she belongs to.

The table definition can mark a column NOT NULL, meaning that a stored row cannot have NULL in that column. An IS NULL query on such a column returns no rows. Check this rule when inspecting a schema: columns that permit missing data need special attention when you build exclusions or combine conditions.

A condition in SQL can be true, false, or unknown. Missing values make the third state necessary: without a recorded quantity, a database cannot establish whether that quantity is above a threshold.

A condition and its opposite

For Bookshelf, stock > 10 is unknown because stock is NULL. So is stock <= 10. WHERE keeps only true conditions and drops both false and unknown ones.

In the products table, stock > 10 returns four rows and stock <= 10 returns three. Together they cover seven of the eight products. Bookshelf is absent from both. Applying NOT does not restore it: NOT unknown is unknown, so NOT (stock > 10) still selects only the three known counts at most 10.

This differs from a value that fails a condition. Lamp Shade's known zero makes stock > 10 false, so NOT turns it true. A missing count supplies no such definite comparison to reverse.

Combine unknown with another test

Bookshelf costs 120.00. In stock > 10 OR price > 100, the price condition is true. OR needs only one true part, so the row is kept even though the stock comparison is unknown. In stock > 10 AND price > 100, the stock condition remains unresolved, so the result is unknown and the row is dropped.

Include missing values deliberately

SELECT first_name
FROM employees
WHERE department <> 'Sales'
   OR department IS NULL;

This returns Ben, David, Farid, Hugo, and Grace. The comparison alone would return four, leaving Grace out. The IS NULL branch explicitly includes staff whose department is unassigned. Whether such staff belong in “outside Sales” is a question about the intended answer; the query must express that choice.

A NULL value in a NOT IN list can block the entire result. department NOT IN ('Sales', NULL) means that the department must differ from Sales and differ from NULL. The latter comparison is unknown for every row, so the full condition is never true. IN still keeps real matches: department IN ('Sales', NULL) selects the three Sales employees, but does not find Grace.

A missing tested value also produces unknown for LIKE and for an ordinary BETWEEN range. IS NULL and IS NOT NULL give definite answers for these scalar columns. Check missing values with those tests separately when a row count seems surprising.

Know when NULLs are grouped

NULL = NULL remains unknown; two missing phone numbers are not known to be the same number. DISTINCT, however, treats repeated missing values as duplicates, and ORDER BY places them together. For a comparison that deliberately treats two NULLs as equal, SQLite and PostgreSQL also provide IS NOT DISTINCT FROM.

ORDER BY sets the order of result rows. Without it, SQL makes no promise about which row appears first, even when a small table seems to return the same order every time.

Choose the direction

ASC is the default. ORDER BY salary and ORDER BY salary ASC ask for the same direction. “Highest paid first” calls for ORDER BY salary DESC.

SELECT first_name, salary
FROM employees
ORDER BY salary DESC;

Read the result from highest to lowest: Farid 83000, Ben 71000, Emma 65000, David 60000, Hugo 58000, Ana 52000, Chloe 48000, and Grace 45000. The database rearranges the result rows; this query does not change the stored table.

Filter before sorting

SELECT name, price
FROM products
WHERE category = 'Lighting'
ORDER BY price;

Only the three Lighting rows enter this result. Their ascending prices produce Lamp Shade 15.50, Desk Lamp 24.99, then Floor Lamp 89.00. ORDER BY follows WHERE in the statement. Reversing those clauses is a syntax error.

The sort column need not be displayed. SELECT first_name FROM employees ORDER BY salary DESC; gives the same name order without showing salaries. PostgreSQL restricts this when DISTINCT is present: a DISTINCT query must select its sort columns.

Read the stored data type

Numeric columns sort by numeric size. Text columns sort by characters under their collation. The text values '10', '9', and '100' sort as '10', '100', '9' under ordinary character ordering. This is not a correct numeric ranking, so check the column type when amounts look out of order.

The example hire_date values are complete fixed-width text dates with the year first. ORDER BY hire_date starts with Farid on 2017-09-12 and ends with Grace on 2024-02-19. DESC reverses that for newest hires first. Month-first text dates such as MM/DD/YYYY compare by month before year and cannot give reliable calendar order across years.

Text order depends on collation

SQLite's default binary collation puts uppercase ASCII letters before lowercase letters. Under that rule, 'Zebra' precedes 'apple', and 'é' comes after 'z'. Many PostgreSQL databases use language-aware collations that produce another order. Consistent capitalization helps, but the database's comparison rules still decide the order of accented and other characters.

Use column names for a clear sort specification. ORDER BY 2 DESC means the second item in the SELECT list, so moving columns can silently change the sort. ORDER BY 'salary' names a text constant, not the salary column: SQLite provides no useful ordering from it, and PostgreSQL rejects it. Write ORDER BY salary.

A second sort column matters only when rows tie on the first. Think of a sorted staff list: group by city first, then put the names within each city in order.

Follow the sort columns from left to right

SELECT last_name, city
FROM employees
ORDER BY city, last_name;

The result begins with Kim and Okafor in Boston. Then come Haddad, Lopez, Nguyen, and O'Neill in Chicago. Brown and Carter in Denver are last. last_name does not compete with city; it decides the order only within a shared city.

Reversing the columns changes the main order. ORDER BY last_name, city mainly sorts by last name and consults city only when last names tie. Use one ORDER BY clause with commas between its sort columns. A second ORDER BY clause is invalid.

Give each column its direction

ORDER BY department, salary DESC means department ascending, then salary descending within each department. DESC belongs only to salary. To make both descending, write department DESC, salary DESC.

In SQLite's default NULL ordering, the first form places Grace's missing department first. IT follows with Farid then Ben; Marketing has David then Hugo; Sales has Emma, Ana, then Chloe. You are reading one hierarchy of comparisons, not first sorting every row by salary and then ignoring that order.

Finish a tie with a unique value

Rows equal on every sort column can appear in either order. ORDER BY city does not settle which Chicago employee comes first. A complete sort order adds a last column that uniquely identifies each row:

SELECT first_name, city
FROM employees
ORDER BY city, employee_id;

The IDs resolve every city tie. For unchanged data, this makes a later LIMIT or page select a repeatable part of the ordered list. A unique tie-breaker fixes which rows appear at a cutoff; it does not include extra tied rows.

Position missing values deliberately

Database defaults differ:

So the department query puts Grace last in PostgreSQL under its default, even though it puts her first in SQLite. This difference is separate from the salary direction.

SELECT first_name, department, salary
FROM employees
ORDER BY department NULLS LAST, salary DESC;

SQLite 3.30 or later, PostgreSQL, and Oracle support NULLS FIRST and NULLS LAST. Here, they put Grace after all recorded departments while preserving salary order within each department. MySQL and SQL Server do not accept those clauses.

Translate a requested hierarchy in the same sequence it is spoken: “by department, highest paid first within each department” gives department, salary DESC. “Alphabetically by last name, then first name” gives last_name, first_name. Add an ID last when you need repeatable order among people with identical names.

LIMIT keeps at most a chosen number of result rows. It does not decide which rows deserve to be first. Use ORDER BY to give a shortlist a meaning such as highest paid, newest, or cheapest.

Sort, then cut

SELECT first_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

The sorted salaries begin with Farid 83000, Ben 71000, and Emma 65000, so those three rows remain. Without ORDER BY, LIMIT 3 returns some three rows, with no promise that they are the highest earners.

WHERE works before the limit. WHERE city = 'Boston' ORDER BY salary DESC LIMIT 5 returns only David and Grace because just two employees qualify. LIMIT is a maximum, not a request to invent extra rows. LIMIT 0 returns none.

Skip rows for a later page

OFFSET skips rows before LIMIT counts the rows to return. ORDER BY salary DESC LIMIT 3 OFFSET 3 skips Farid, Ben, and Emma, then returns David, Hugo, and Ana: positions four through six.

For page k with n rows per page, skip (k − 1) × n rows. Page three at three rows per page uses LIMIT 3 OFFSET 6. Only Chloe and Grace remain. The first page has offset zero, not one.

“Second row” and “second different value” are different requests. ORDER BY salary DESC LIMIT 1 OFFSET 1 returns Ben with 71000 here. If two employees shared the top salary, that query would return another top earner. To find the second different salary, select DISTINCT salary before sorting and skipping.

Settle the cutoff

ORDER BY city LIMIT 3 includes the two Boston employees and one Chicago employee, but does not specify which Chicago employee. ORDER BY city, employee_id LIMIT 3 selects David, Grace, and Ana. The unique ID resolves the cutoff.

To keep everyone tied at the final sort value, PostgreSQL 13 or later and Oracle support FETCH FIRST 3 ROWS WITH TIES. With ORDER BY city, it keeps six rows: both Boston rows and all four Chicago rows. If the ORDER BY also includes a unique ID, rows cannot tie on the complete sort key.

Recognize the database's syntax

Standard SQL uses OFFSET and FETCH, not LIMIT. PostgreSQL and Oracle accept OFFSET 3 ROWS FETCH FIRST 3 ROWS ONLY. SQL Server uses ORDER BY salary DESC OFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY; ORDER BY is required for this paging form.

SQLite and MySQL also accept LIMIT 6, 3, with the offset first. Prefer LIMIT 3 OFFSET 6 so the roles are explicit. Paging runs a query again for each page. A complete sort order keeps pages consistent while data stays unchanged; inserts or deletions between requests can still make pages repeat or skip records.

SQL clauses have a fixed written order. Their logical order explains the result as a sequence of decisions, even when the database uses a different internal execution plan.

Write the statement in clause order

SELECT DISTINCT city
FROM employees
WHERE department = 'Sales'
ORDER BY city
LIMIT 2 OFFSET 0;

SELECT and FROM identify the requested columns and source table. The other clauses are optional, but when present they keep this order: WHERE, ORDER BY, LIMIT, then OFFSET in this LIMIT form. DISTINCT sits directly after SELECT.

Use one WHERE clause with several joined conditions. A second WHERE is a syntax error. The same applies to ORDER BY: put several sort columns in one clause, separated by commas. Moving WHERE below ORDER BY does not ask for a different processing order; it produces invalid SQL.

Trace the logical decisions

The result is Chicago, then Denver. The repeated city disappears before the limit counts result rows. You are not taking two employees and then asking whether they live in different cities.

The optimizer may use a different physical execution plan that produces the same result as this logical sequence.

Use columns where they are available

WHERE tests the source rows before SELECT chooses displayed columns:

SELECT first_name
FROM employees
WHERE salary > 60000;

This works without salary in the output. The qualifying names are Ben, Emma, and Farid. Salary is still a column of each source row when the test is made.

Likewise, filtering before limiting is essential for a top result within a group:

SELECT first_name
FROM employees
WHERE department = 'Sales'
ORDER BY salary DESC
LIMIT 1;

The answer is Emma. Sales staff qualify first; their salaries are then ranked. Taking the company's top earner first and testing that single row for Sales would miss the intended answer, because Farid belongs to IT.

Make the structure visible

Write one clause per line and place longer conditions on separate indented lines. For “Furniture products below 200 dollars, highest price first,” use:

SELECT name, price
FROM products
WHERE category = 'Furniture'
  AND price < 200
ORDER BY price DESC;

Office Chair at 149.00 comes before Bookshelf at 120.00. The two conditions share one WHERE, the numeric threshold has no currency sign, and ORDER BY sorts the rows that passed. Checking each line against its role makes both syntax errors and wrong interpretations easier to locate.

Before writing SQL, identify the separate decisions hidden in an everyday request. Each decision belongs to a clause, and each clause can be checked against the intended answer.

Make six decisions

1. Which table contains the records? Choose FROM.
2. Which records qualify? Build WHERE.
3. Which fields answer the question? Choose SELECT.
4. Are repeated result values useful? Decide on DISTINCT.
5. What should come first? Build ORDER BY.
6. Is there a maximum or page? Add LIMIT and OFFSET.

For “at least,” include equality. “One of” suggests IN, “contains” suggests a LIKE pattern with percent signs at both ends, “missing” suggests IS NULL, and “different values” suggests DISTINCT. A top-n request needs both a sort direction and a limit.

Build a staff shortlist

“Show the names and hire dates of the three newest hires outside Sales who have a phone on file, newest first.” Include unassigned staff in this request's meaning of outside Sales.

Start with employees. The qualifying department rule is department <> 'Sales' OR department IS NULL. Group that choice so the phone rule applies to both branches. Show names and dates, then rank the qualifying rows:

SELECT first_name, last_name, hire_date
FROM employees
WHERE (department <> 'Sales' OR department IS NULL)
  AND phone IS NOT NULL
ORDER BY hire_date DESC
LIMIT 3;

Grace Okafor on 2024-02-19 is first, David Kim on 2020-11-23 second, and Farid Haddad on 2017-09-12 third. Removing the IS NULL branch loses Grace and leaves only two results. For an unclear request, settle how missing departments should count and state the choice with the answer.

Combine category, price, and stock

“Which Lighting or Storage products cost less than 20 dollars and are in stock, cheapest first?” Use IN for the two category choices and require a known positive quantity:

SELECT name, price
FROM products
WHERE category IN ('Lighting', 'Storage')
  AND price < 20
  AND stock > 0
ORDER BY price;

Paper Tray at 9.99 comes before Desk Organizer at 18.75. Lamp Shade is cheap enough and in a chosen category, but its stock is zero, so it fails the final test. A missing stock quantity would also fail because it cannot establish that stock is positive.

Check the rows that could expose a mistake

Begin with SELECT * FROM products LIMIT 5; when exploring the schema and a few records. Add filters one at a time, checking changes in the result count and temporarily displaying tested columns when useful.

Test exact boundaries, missing values, text capitalization, and ties at the limit. NOT shows rows where a condition was false; it does not reveal rows where the condition was unknown. Run IS NULL tests separately. Check that your output has the requested columns and that ORDER BY resolves any ties that affect the answer.

When moving the query to another database, check row-limit syntax, equality and LIKE case rules, identifier quoting, date handling, and NULL placement. The examples use LIMIT in SQLite and PostgreSQL; standard SQL's row-limit form is OFFSET with FETCH FIRST.