Basic Queries

SELECT * FROM table_name;

SELECT * FROM users;

-- Returns all columns and rows from the users table

Note Avoid SELECT * in production code. Always specify columns to reduce data transfer and prevent breakage when table schema changes.

SELECT column1, column2 FROM table_name;

SELECT first_name, email FROM users;

-- first_name | email
-- Alice      | [email protected]
-- Bob        | [email protected]

Note Listing columns explicitly improves readability and query performance since the database only fetches what you need.

SELECT columns FROM table WHERE condition;

SELECT first_name, email
FROM users
WHERE is_active = true;

-- Only rows where is_active is true

Note WHERE filters rows before any grouping occurs. Use HAVING to filter after GROUP BY.

SELECT columns FROM table ORDER BY column [ASC|DESC];

SELECT product_name, price
FROM products
ORDER BY price DESC;

-- Products sorted from most expensive to cheapest

Note ASC is the default direction. You can sort by multiple columns: ORDER BY category ASC, price DESC. Sorting happens after WHERE filtering.

SELECT columns FROM table LIMIT count OFFSET skip;

SELECT product_name, price
FROM products
ORDER BY created_at DESC
LIMIT 10 OFFSET 20;

-- Returns rows 21-30 (skips first 20, then takes 10)

Note MySQL/PostgreSQL use LIMIT. SQL Server uses TOP or FETCH FIRST. High OFFSET values degrade performance because the database still scans skipped rows.

SELECT DISTINCT column FROM table;

SELECT DISTINCT city
FROM users
ORDER BY city;

-- Returns each city only once, no duplicates

Note DISTINCT applies to the entire row when used with multiple columns. SELECT DISTINCT city, state treats (city, state) pairs as the unit of uniqueness.

SELECT column AS alias_name FROM table AS t;

SELECT
  u.first_name AS name,
  o.total_amount AS order_total
FROM users AS u
JOIN orders AS o ON o.user_id = u.id;

-- name  | order_total
-- Alice | 149.99

Note AS is optional in most databases but improves clarity. You cannot reference a column alias in the WHERE clause of the same query — use a subquery or CTE instead.

SELECT expression AS alias FROM table;

SELECT
  product_name,
  price,
  quantity,
  price * quantity AS line_total
FROM order_items;

-- product_name | price | quantity | line_total
-- Widget       | 25.00 | 3        | 75.00

Note You can use arithmetic operators (+, -, *, /), string functions, and CASE expressions directly in the SELECT list.

-- PostgreSQL / SQL Server
SELECT columns INTO new_table FROM source_table;
-- MySQL
CREATE TABLE new_table AS SELECT columns FROM source_table;

CREATE TABLE active_users AS
SELECT id, first_name, email
FROM users
WHERE is_active = true;

-- Creates a new table active_users with matching rows

Note This copies data but not indexes or constraints. MySQL uses CREATE TABLE ... AS SELECT, while PostgreSQL and SQL Server support SELECT ... INTO.

SELECT columns FROM table
ORDER BY column
FETCH FIRST n ROWS ONLY;

SELECT product_name, price
FROM products
ORDER BY price DESC
OFFSET 5 ROWS
FETCH FIRST 10 ROWS ONLY;

-- Skips 5, returns next 10 most expensive products

Note FETCH FIRST is the ANSI SQL standard way to limit results. PostgreSQL supports both FETCH FIRST and LIMIT. MySQL only supports LIMIT. Use FETCH FIRST for maximum portability.