SELECT — Pulling Data Out of a Table

About the SQL covered in this course

This course teaches the universal SQL syntax that works across major RDBMSs — MySQL, Oracle, PostgreSQL, SQL Server, and more. The basics like CREATE TABLE / SELECT / WHERE / JOIN / GROUP BY write the same way no matter which database you switch to.

For technical reasons the in-browser console runs SQLite, but whenever a feature differs between databases (length enforcement on types, how to list tables, etc.) you'll see a side-by-side note like In MySQL you'd write... so you know what to use elsewhere.

SELECT — pull all columns

To pull data out of a table, you use the SELECT statement. SELECT is the most-used construct in SQL: report generation, screen rendering, data analysis, batch processing — almost every operation that "reads" data starts with SELECT.

Let's start with the form that pulls all columns of a table as-is. The * (asterisk) in SELECT * is a symbol for "all columns," and the FROM table_name that follows specifies which table to pull from.

-- Pull all columns and all rows from the user table
SELECT * FROM user;

Pull all columns and all rows from the user table set up at the top of the article. (Run it correctly and the explanation will appear.)

① Write and run SELECT * FROM user;.

② Confirm the result returns 5 rows with the four columns id / name / age / city.

Pulling specific columns only

SELECT * is convenient, but in real apps it's often better to pull only the columns you need. The volume of data sent and received drops, only the columns you want to display come back, and downstream processing stays simple.

To pick columns, list the column names separated by commas right after SELECT. The order you write them is the order they appear in the result.

-- Pull just name and age (2 columns)
SELECT name, age FROM user;

-- You can also reorder columns
SELECT city, name FROM user;

Imagine a user-list screen that should only show "name and age."

① From the user table, pull just the name and age columns in that order.

② Confirm the result is 5 rows × 2 columns and that id and city are not included.

AS — giving a column an alias

By default, the column header in the result of a SELECT shows the original column name. With column AS alias you can change the result-side column name to anything you want. This doesn't change the column name in the underlying table — it's just "what should this column be called in this SELECT result."

Aliases are also handy when you want to name an expression. Arithmetic like age + 1 or aggregate-function results like SUM(price) don't have a default column name, so without AS the header would be the raw expression — awkward to display or work with from your application code.

-- Alias columns
SELECT name AS user_name, age AS user_age FROM user;

-- Alias an expression (computing next year's age)
SELECT name, age + 1 AS next_year_age FROM user;

AS can be omitted

SELECT name AS user_name FROM user; and SELECT name user_name FROM user; (with AS dropped) mean the same thing. That said, writing AS makes "this is now an alias" obvious to the reader, so this course always writes AS explicitly.

If you need spaces or non-ASCII in the alias, wrap it in double quotes: AS "Next Year's Age".

Imagine a user-list screen that should display "current age" alongside "next year's age."

① From the user table, pull name, age, and age + 1.

② Use AS to alias the third column (age + 1) as next_year_age.

③ Confirm the result is 5 rows × 3 columns and that the header of the third column is next_year_age.

WHERE — filtering rows by condition

So far you've learned how to pick columns. Now let's look at filtering rows. By appending WHERE condition after SELECT cols FROM table, only rows that satisfy the condition end up in the result.

It helps to think of SELECT as choosing vertically (columns) and WHERE as choosing horizontally (rows).

Comparison operators

Inside WHERE, conditions use comparison operators (symbols that compare values and return true/false). SQL's comparison operators look much like other programming languages, with one catch: equality is a single =, not ==.

-- Pull users aged 30 and over
SELECT * FROM user WHERE age >= 30;

-- Pull rows where city is Tokyo (strings go in single quotes)
SELECT * FROM user WHERE city = 'Tokyo';

-- Pull rows where city is anything other than Tokyo
SELECT * FROM user WHERE city <> 'Tokyo';

String comparison with `=` is case-sensitive

In this course's console, string comparison with `=` is case-sensitive. WHERE name = 'alice' won't match 'Alice' — the result is 0 rows. In MySQL, the collation often makes this comparison case-insensitive by default, so the same query may return 1 row.

`WHERE column = NULL` doesn't do what you expect

Writing WHERE name = NULL won't pull "rows where name is NULL." In SQL, NULL is treated as "unknown," and = NULL always evaluates to NULL — which WHERE filters out. To pull NULL rows, use the dedicated WHERE column IS NULL. We cover this in detail in the WHERE deep-dive article — for now, just remember "don't use = NULL."

Imagine a requirement: "show every user aged 30 or over."

① From the user table, pull every row where age is 30 or higher.

② Confirm the result is 3 rows (Alice / Carol / Eve) and that Bob and Dave are not included.

AND / OR — combining multiple conditions

In real work you often need to combine conditions: "users in Tokyo aged 30 and over," "members with rank gold or platinum." SQL combines conditions with AND (both true) and OR (either true).

One more useful operator is NOT (negate the condition). NOT (city = 'Tokyo') means "not Tokyo." <> does the same job, so for now just focus on AND and OR.

-- Users in Tokyo aged 30 and over
SELECT name, age, city FROM user WHERE city = 'Tokyo' AND age >= 30;

-- Users in Tokyo or Osaka
SELECT name, city FROM user WHERE city = 'Tokyo' OR city = 'Osaka';

Use parentheses when mixing AND and OR

When AND and OR appear in the same WHERE, AND is evaluated before OR. A OR B AND C actually means A OR (B AND C) — not the order you wrote.

To avoid surprises, make evaluation order explicit with parentheses when mixing AND and OR. For example, "(Tokyo or Osaka) and 30 and over" is WHERE (city = 'Tokyo' OR city = 'Osaka') AND age >= 30. Parentheses also make your intent clear to readers — when in doubt, add them.

Imagine a requirement: "list the name and city of users who live in Tokyo or Osaka, are 25 and over, and are not Carol." This exercise practices combining AND, OR, and NOT.

① From the user table, pull name and city.

② Use a WHERE that combines three conditions with AND: `city` is `'Tokyo'` or `'Osaka'`, `age` is 25 or over, and `name` is not `'Carol'`. Make the OR portion's evaluation order explicit with parentheses.

③ Confirm the result is 3 rows (Alice Tokyo / Bob Osaka / Eve Tokyo), with Carol (Tokyo / 35) and Dave (Kyoto / 28) excluded.