If you’ve spent any time working with databases, you already know that SELECT is the command you’ll type more than any other. It’s the workhorse of SQL. Every report you generate, every dashboard you build, every quick check you run to see if your data looks right — it all starts with SELECT. In this guide, I’m going to walk you through everything you need to know about the SELECT query in SQLite, from the absolute basics to the tricks that separate a beginner from someone who actually knows what they’re doing.
I chose SQLite for this walkthrough because it’s the friendliest database engine to learn on. There’s no server to install, no username or password to remember, and no complicated configuration file to fight with. You just open a file (or even work entirely in memory) and start querying. That simplicity makes it perfect for learning the concepts that apply to virtually every relational database out there, including PostgreSQL, MySQL, and SQL Server.
What SELECT Actually Does
At its core, SELECT retrieves rows of data from one or more tables. It doesn’t change anything, it doesn’t delete anything, it just reads. Think of a table as a spreadsheet: rows are records, columns are fields. SELECT lets you pick which columns you want to see, which rows match your criteria, and how you want the results ordered.
Here’s the most basic form:
SELECT column1, column2 FROM table_name;
And if you want every column, you use the asterisk:
SELECT * FROM table_name;
I’ll be honest with you: SELECT * is convenient when you’re exploring a database for the first time, but it’s a habit worth breaking once you’re writing real application code. Pulling every column when you only need two or three wastes bandwidth and makes your queries harder to reason about. I’ll get into why that matters more later in this article.
Setting Up a Table to Practice On
Before we go further, let’s create something to query against. Open a terminal, type sqlite3 practice.db, and run this:
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
first_name TEXT,
last_name TEXT,
department TEXT,
salary REAL,
hire_date TEXT
);
INSERT INTO employees (first_name, last_name, department, salary, hire_date) VALUES
('Sara', 'Khan', 'Engineering', 95000, '2021-03-14'),
('Bilal', 'Ahmed', 'Sales', 62000, '2019-07-01'),
('Ayesha', 'Malik', 'Engineering', 105000, '2020-11-23'),
('Usman', 'Raza', 'Marketing', 58000, '2022-01-09'),
('Fatima', 'Sheikh', 'Sales', 71000, '2018-05-30'),
('Hassan', 'Iqbal', 'Engineering', 88000, '2023-02-17');
Now we’ve got a small dataset to run examples against for the rest of this article.
Selecting Specific Columns
If you only care about names and salaries, don’t pull everything:
SELECT first_name, last_name, salary FROM employees;
This returns exactly three columns per row. It’s cleaner, faster, and easier to read in your terminal or application output.
Filtering Rows with WHERE
SELECT becomes genuinely useful once you add the WHERE clause, which filters rows based on a condition.
SELECT first_name, last_name, department
FROM employees
WHERE department = 'Engineering';
You can combine conditions with AND and OR:
SELECT first_name, last_name, salary
FROM employees
WHERE department = 'Engineering' AND salary > 90000;
SELECT first_name, last_name
FROM employees
WHERE department = 'Sales' OR department = 'Marketing';
A cleaner way to write multiple OR conditions on the same column is IN:
SELECT first_name, last_name
FROM employees
WHERE department IN ('Sales', 'Marketing');
There’s also NOT IN, BETWEEN, and LIKE, all of which come in handy constantly:
SELECT first_name, salary
FROM employees
WHERE salary BETWEEN 60000 AND 100000;
SELECT first_name, last_name
FROM employees
WHERE last_name LIKE 'K%';
The % in LIKE is a wildcard that matches any sequence of characters, so 'K%' matches any last name starting with K. There’s also _, which matches exactly one character.
Sorting Results with ORDER BY
By default, SQLite doesn’t guarantee any particular row order unless you tell it to. ORDER BY fixes that.
SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC;
DESC sorts highest to lowest; ASC (the default, so you can omit it) sorts lowest to highest. You can sort by multiple columns too:
SELECT first_name, last_name, department, salary
FROM employees
ORDER BY department ASC, salary DESC;
This sorts by department alphabetically first, then within each department, sorts by salary from highest to lowest. I use this pattern constantly when building reports that need to be grouped visually.
Limiting Results with LIMIT and OFFSET
If you only want the top few rows, LIMIT is your friend:
SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;
That gives you the three highest-paid employees. Combine it with OFFSET to build pagination:
SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3 OFFSET 3;
This skips the first three rows and returns the next three — exactly the kind of thing you’d use to build “page 2” of a results list in a web app.
Removing Duplicates with DISTINCT
Sometimes a query returns repeated values you don’t care to see more than once. Say you want a list of every department represented in the table:
SELECT DISTINCT department FROM employees;
Without DISTINCT, you’d get “Engineering” listed three times because three employees work there. DISTINCT collapses duplicates down to unique values.
Aggregate Functions: COUNT, SUM, AVG, MIN, MAX
This is where SELECT starts feeling like real analysis instead of just data retrieval. SQLite ships with several built-in aggregate functions.
SELECT COUNT(*) FROM employees;
That tells you how many rows are in the table. Want the average salary?
SELECT AVG(salary) FROM employees;
Total payroll:
SELECT SUM(salary) FROM employees;
Highest and lowest salary:
SELECT MAX(salary), MIN(salary) FROM employees;
These become far more powerful when paired with GROUP BY.
Grouping Data with GROUP BY
GROUP BY lets you run aggregate functions per category instead of across the whole table. If I want the average salary per department:
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;
That AS keyword renames the output column to something more readable — this is called an alias. Without it, the column header would just show the raw expression, which isn’t great when you’re looking at query results or handing them off to another tool.
You can also count employees per department:
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;
Filtering Groups with HAVING
Here’s something that trips up a lot of beginners: WHERE filters rows before grouping, but you can’t use WHERE to filter on an aggregate result like AVG(salary), because that value doesn’t exist until after grouping happens. That’s what HAVING is for.
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 70000;
This only shows departments where the average salary is above 70,000 — something you simply cannot do with WHERE alone.
Joining Tables
Real databases rarely keep everything in one table. Let’s add a departments table with more detail:
CREATE TABLE departments (
id INTEGER PRIMARY KEY,
name TEXT,
manager TEXT
);
INSERT INTO departments (name, manager) VALUES
('Engineering', 'Zainab Tariq'),
('Sales', 'Omar Farooq'),
('Marketing', 'Nadia Hussain');
Now I can join the two tables to pull related information together:
SELECT employees.first_name, employees.last_name, departments.manager
FROM employees
JOIN departments ON employees.department = departments.name;
This is an INNER JOIN (JOIN by itself defaults to INNER JOIN), meaning it only returns rows where there’s a match in both tables. There’s also LEFT JOIN, which returns every row from the left table even if there’s no match on the right:
SELECT employees.first_name, departments.manager
FROM employees
LEFT JOIN departments ON employees.department = departments.name;
SQLite doesn’t support RIGHT JOIN or FULL OUTER JOIN natively (as of most stable versions), so if you need that behavior, you typically flip the table order and use LEFT JOIN instead, or emulate FULL OUTER JOIN with a UNION of two LEFT JOINs.
Using Table Aliases
When your queries involve joins, typing out full table names repeatedly gets tedious. Aliases fix that:
SELECT e.first_name, e.last_name, d.manager
FROM employees AS e
JOIN departments AS d ON e.department = d.name;
Shorter, cleaner, and still perfectly readable once you’re used to it.
Subqueries
A subquery is a SELECT statement nested inside another query. They’re incredibly useful when you need a value calculated first before filtering on it.
SELECT first_name, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
This finds every employee earning more than the company-wide average. The inner query runs first, producing a single number, and the outer query uses that number in its WHERE clause.
Subqueries can also appear in the FROM clause, effectively acting as a temporary table:
SELECT department, avg_salary
FROM (
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
)
WHERE avg_salary > 75000;
CASE Expressions
Sometimes you want conditional logic right inside your SELECT statement. CASE handles that:
SELECT first_name, salary,
CASE
WHEN salary >= 90000 THEN 'Senior'
WHEN salary >= 65000 THEN 'Mid'
ELSE 'Junior'
END AS pay_tier
FROM employees;
This adds a computed column classifying each employee by pay tier, without changing anything in the underlying table.
Common Mistakes to Avoid
I’ve seen (and made) all of these myself when I was starting out:
Forgetting that string comparisons in SQLite are case-sensitive by default for = but not always for LIKE. WHERE department = 'engineering' won’t match 'Engineering', but WHERE department LIKE 'engineering' will, because LIKE is case-insensitive for ASCII characters by default in SQLite.
Mixing up WHERE and HAVING. Remember: WHERE filters individual rows before grouping, HAVING filters groups after aggregation.
Using SELECT * in production code. It’s fine for quick exploration, but explicit column lists protect you when the table schema changes later — adding a new column to a table won’t silently break application code that expects specific columns in a specific order.
Not indexing columns used in WHERE and JOIN clauses on large tables. SELECT itself doesn’t create indexes, but query performance depends heavily on whether one exists. On a table with a few thousand rows you won’t notice. On a table with millions of rows, an unindexed WHERE clause can turn a query that should take milliseconds into one that takes several seconds.
Best Practices Worth Adopting Early
Write your SQL keywords in uppercase (SELECT, FROM, WHERE) even though SQLite doesn’t require it. It makes queries far easier to scan visually, especially once they get long.
Format multi-line queries with each clause on its own line, like I’ve done throughout this article. It costs you nothing and saves you real time when debugging.
Always alias your aggregate columns. AVG(salary) as a column header is confusing; avg_salary is not.
Test filters incrementally. If you’re building a complex query with several WHERE conditions, joins, and a GROUP BY, build it up one piece at a time and check the output at each stage rather than writing the whole thing and hoping it works.
Use EXPLAIN QUERY PLAN in front of any SELECT statement you’re worried about performance-wise:
EXPLAIN QUERY PLAN
SELECT * FROM employees WHERE department = 'Engineering';
This tells you whether SQLite is doing a full table scan or using an index, which is invaluable once your tables grow beyond toy examples.
Wrapping Up
SELECT is deceptively simple to introduce and genuinely deep to master. Everything else in SQL — joins, subqueries, aggregate functions, window functions — exists to make SELECT more expressive. If you get comfortable with the fundamentals covered here — filtering, sorting, grouping, and joining — you’ll be able to answer almost any question your data can throw at you. The best way to actually internalize all of this is to open a terminal right now, load up a small dataset of your own, and start writing queries. You’ll learn far more from twenty minutes of hands-on practice than from reading ten more articles like this one.
