๐Ÿ—„๏ธ SQL Dictionary

Every keyword, operator, and symbol โ€” with plain-English explanations.

DML SQL Commands
Data Manipulation Language commands are used to add, delete, update, and retrieve data from a database.
SELECT * FROM users;
INSERT-INTO SQL Commands
Data Manipulation Language commands are used to add, delete, update, and retrieve data from a database.
INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com');
SELECT dml
Retrieves rows from one or more tables; the core read statement in SQL
SELECT id, name FROM users WHERE active = true;
INSERT dml
Adds one or more rows into a table
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
VALUES SQL Commands
Data Manipulation Language commands are used to add, delete, update, and retrieve data from a database.
VALUES ('John Doe', 'john@example.com');
DEFAULT SQL Commands
Data Manipulation Language commands are used to add, delete, update, and retrieve data from a database.
DEFAULT VALUES;
ON-CONFLICT-DO-NOTHING SQL Commands
Data Manipulation Language commands are used to add, delete, update, and retrieve data from a database.
ON CONFLICT (name) DO NOTHING;
CREATE TABLE ddl
Defines a new table with columns, data types, and constraints
CREATE TABLE posts (id SERIAL PRIMARY KEY, title TEXT NOT NULL, created_at TIMESTAMPTZ DEFAULT NOW());
ON-CONFLICT-DO-UPDATE SQL Commands
Data Manipulation Language commands are used to add, delete, update, and retrieve data from a database.
ON CONFLICT (name) DO UPDATE SET email = EXCLUDED.email;
ALTER TABLE ddl
Modifies an existing table: add/drop columns, rename, change types, add constraints
ALTER TABLE users ADD COLUMN phone TEXT;
RETURNING SQL Commands
Data Manipulation Language commands are used to add, delete, update, and retrieve data from a database.
RETURNING name, email;
UPDATE SQL Commands
Data Manipulation Language commands are used to add, delete, update, and retrieve data from a database.
UPDATE users SET email = 'newemail@example.com' WHERE name = 'John Doe';
CREATE INDEX ddl
Creates an index on one or more columns to speed up query lookups
CREATE INDEX idx_users_email ON users (email);
SET SQL Commands
Data Manipulation Language commands are used to add, delete, update, and retrieve data from a database.
SET email = 'newemail@example.com';
FROM SQL Commands
Data Manipulation Language commands are used to add, delete, update, and retrieve data from a database.
FROM users;
WHERE clause
Filters rows based on a condition; applied before grouping
SELECT * FROM orders WHERE total > 100 AND status = 'paid';
DELETE SQL Commands
Data Manipulation Language commands are used to add, delete, update, and retrieve data from a database.
DELETE FROM users WHERE name = 'John Doe';
JOIN clause
Combines rows from two tables based on a related column; INNER JOIN by default
SELECT u.name, o.total FROM users u JOIN orders o ON u.id = o.user_id;
LEFT JOIN clause
Returns all rows from the left table and matched rows from the right; NULLs for no match
SELECT u.name, o.id FROM users u LEFT JOIN orders o ON u.id = o.user_id;
USING SQL Commands
Data Manipulation Language commands are used to add, delete, update, and retrieve data from a database.
USING orders;
GROUP BY clause
Groups rows with the same values into summary rows; used with aggregate functions
SELECT status, COUNT(*) FROM orders GROUP BY status;
TRUNCATE-TABLE SQL Commands
Data Manipulation Language commands are used to add, delete, update, and retrieve data from a database.
TRUNCATE TABLE users;
CASCADE SQL Commands
Data Manipulation Language commands are used to add, delete, update, and retrieve data from a database.
TRUNCATE TABLE users CASCADE;
HAVING clause
Filters groups after GROUP BY; equivalent to WHERE but for aggregated data
SELECT user_id, COUNT(*) FROM orders GROUP BY user_id HAVING COUNT(*) > 5;
ORDER BY clause
Sorts the result set by one or more columns; ASC (default) or DESC
SELECT name, score FROM players ORDER BY score DESC LIMIT 10;
RESTART-IDENTITY SQL Commands
Data Manipulation Language commands are used to add, delete, update, and retrieve data from a database.
RESTART IDENTITY;
LIMIT clause
Restricts the number of rows returned by a query
SELECT * FROM logs ORDER BY created_at DESC LIMIT 100;
MERGE-INTO SQL Commands
Data Manipulation Language commands are used to add, delete, update, and retrieve data from a database.
MERGE INTO users u USING orders o ON u.name = o.user_name;
INNER-JOIN Joins
Inner join returns all rows when there is a match in both tables.
SELECT u.name, o.product FROM users u INNER JOIN orders o ON u.name = o.user_name;
OFFSET clause
Skips a specified number of rows before beginning to return rows
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 40;
DISTINCT keyword
Eliminates duplicate rows from the result set
SELECT DISTINCT country FROM users;
LEFT-JOIN Joins
Left join returns all rows from the left table, and the matched rows from the right table. The result is NULL from the right side, if there is no match.
SELECT u.name, o.product FROM users u LEFT JOIN orders o ON u.name = o.user_name;
COUNT aggregate
Returns the number of rows matching a condition; COUNT(*) counts all rows
SELECT COUNT(*) FROM users;
LEFT-OUTER-JOIN Joins
Left outer join returns all rows from the left table, and the matched rows from the right table. The result is NULL from the right side, if there is no match.
SELECT u.name, o.product FROM users u LEFT OUTER JOIN orders o ON u.name = o.user_name;
RIGHT-JOIN Joins
Right join returns all rows from the right table, and the matched rows from the left table. The result is NULL from the left side, if there is no match.
SELECT u.name, o.product FROM users u RIGHT JOIN orders o ON u.name = o.user_name;
SUM aggregate
Returns the total sum of a numeric column
SELECT SUM(total) FROM orders WHERE status = 'paid';
AVG aggregate
Returns the average value of a numeric column
SELECT AVG(score) FROM test_results;
RIGHT-OUTER-JOIN Joins
Right outer join returns all rows from the right table, and the matched rows from the left table. The result is NULL from the left side, if there is no match.
SELECT u.name, o.product FROM users u RIGHT OUTER JOIN orders o ON u.name = o.user_name;
FULL-OUTER-JOIN Joins
Full outer join returns all rows when there is a match in either left or right table.
SELECT u.name, o.product FROM users u FULL OUTER JOIN orders o ON u.name = o.user_name;
MIN aggregate
Returns the minimum value of a column
SELECT MIN(price) FROM products WHERE category = 'books';
CROSS-JOIN Joins
Cross join returns the Cartesian product of the two tables, which means that every row in the first table is combined with every row in the second table.
SELECT u.name, o.product FROM users u CROSS JOIN orders o;
MAX aggregate
Returns the maximum value of a column
SELECT MAX(created_at) FROM posts WHERE user_id = 42;
COALESCE function
Returns the first non-NULL value from a list of expressions
SELECT COALESCE(phone, email, 'no contact') FROM users;
NATURAL-JOIN Joins
Natural join returns the combination of two tables, where the join condition is determined by the common column names in the two tables.
SELECT u.name, o.product FROM users u NATURAL JOIN orders o;
JOIN-USING Joins
Join using returns the combination of two tables, where the join condition is specified using the USING clause.
SELECT u.name, o.product FROM users u JOIN orders o USING (user_name);
NULLIF function
Returns NULL if two expressions are equal, otherwise returns the first expression
SELECT NULLIF(score, 0) FROM results;
CASE expression
Evaluates conditions and returns a value for the first true condition (like if/else)
SELECT name, CASE WHEN score >= 90 THEN 'A' ELSE 'B' END FROM results;
SELF-JOIN Joins
Self join is a type of join where a table is joined with itself.
SELECT u1.name, u2.name FROM users u1 JOIN users u2 ON u1.manager = u2.name;
CREATE-TABLE DDL
Create table statement is used to create a new table in the database.
CREATE TABLE users (id SERIAL PRIMARY KEY, name VARCHAR(100), email VARCHAR(100));
IN operator
Tests whether a value matches any value in a list or subquery
SELECT * FROM users WHERE role IN ('admin','moderator');
CREATE-TABLE-IF-NOT-EXISTS DDL
Create table statement is used to create a new table in the database if it does not already exist.
CREATE TABLE IF NOT EXISTS users (id SERIAL PRIMARY KEY, name VARCHAR(100), email VARCHAR(100));
CREATE-TABLE-AS-SELECT DDL
Create table as select statement is used to create a new table with the data from a select statement.
CREATE TABLE users AS SELECT * FROM users_backup;
LIKE operator
Pattern matching with % (any chars) and _ (single char)
SELECT * FROM products WHERE name LIKE 'Apple%';
CREATE-INDEX DDL
Create index statement is used to create a new index on a table to improve query performance.
CREATE INDEX idx_name ON users (name);
ILIKE operator
Case-insensitive version of LIKE (PostgreSQL)
SELECT * FROM posts WHERE title ILIKE '%javascript%';
BETWEEN operator
Tests whether a value is within a range (inclusive of both endpoints)
SELECT * FROM orders WHERE total BETWEEN 100 AND 500;
CREATE-UNIQUE-INDEX DDL
Create unique index statement is used to create a new unique index on a table to enforce uniqueness of a column or columns.
CREATE UNIQUE INDEX idx_unique ON users (email);
CREATE-INDEX-CONCURRENTLY DDL
Create index concurrently statement is used to create an index in a way that does not lock the table during the creation process.
CREATE INDEX CONCURRENTLY idx_name ON users (name);
EXISTS operator
Returns true if a subquery returns any rows
SELECT * FROM users WHERE EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id);
CREATE-VIEW DDL
Create view statement is used to create a virtual table that can be queried like a regular table, but its contents are defined by a query.
CREATE VIEW user_summary AS SELECT name, COUNT(*) AS orders FROM users JOIN orders ON users.name = orders.user_name GROUP BY name;
UNION set-op
Combines results of two queries, removing duplicates; UNION ALL keeps duplicates
SELECT name FROM customers UNION SELECT name FROM suppliers;
CREATE-MATERIALIZED-VIEW DDL
Create materialized view statement is used to create a persistent, precomputed table that can be queried like a regular table, but its contents are stored in the database and can be refreshed later.
CREATE MATERIALIZED VIEW user_summary AS SELECT name, COUNT(*) AS orders FROM users JOIN orders ON users.name = orders.user_name GROUP BY name;
WITH cte
Defines a Common Table Expression (CTE) โ€” a named temporary result set
WITH active AS (SELECT id FROM users WHERE active = true)
SELECT * FROM orders WHERE user_id IN (SELECT id FROM active);
CREATE-SEQUENCE DDL
Create sequence statement is used to create a new sequence object that can be used to generate unique values for a column.
CREATE SEQUENCE user_id_seq START WITH 1 INCREMENT BY 1;
CREATE-TYPE-AS-ENUM DDL
Create type as enum statement is used to create a new enumerated type in the database.
CREATE TYPE gender AS ENUM ('male', 'female', 'other');
ROW_NUMBER window-fn
Assigns a unique sequential integer to rows within a window partition
SELECT name, ROW_NUMBER() OVER (ORDER BY score DESC) AS rank FROM players;
CREATE-TYPE-AS-SET DDL
Create type as set statement is used to create a new set type in the database.
CREATE TYPE tags AS SET OF VARCHAR;
RANK window-fn
Assigns a rank to rows; ties get the same rank and next rank is skipped
SELECT name, RANK() OVER (ORDER BY score DESC) FROM leaderboard;
CREATE-TYPE-AS-ARRAY DDL
Create type as array statement is used to create a new array type in the database.
CREATE TYPE tags_array AS ARRAY [VARCHAR];
ALTER-TABLE DDL
Alter table statement is used to modify the structure of an existing table.
ALTER TABLE users ADD COLUMN age INTEGER;
DROP-TABLE DDL
Drop table statement is used to remove a table from the database.
DROP TABLE users;
DROP-INDEX DDL
Drop index statement is used to remove an index from a table.
DROP INDEX idx_name;
PRIMARY KEY constraint
Uniquely identifies each row in a table; implicitly creates a unique index
CREATE TABLE users (id SERIAL PRIMARY KEY, email TEXT);
DROP-VIEW DDL
Drop view statement is used to remove a view from the database.
DROP VIEW user_summary;
FOREIGN KEY constraint
Enforces referential integrity between tables
CREATE TABLE orders (id SERIAL PRIMARY KEY, user_id INT REFERENCES users(id) ON CASCADE);
DROP-SEQUENCE DDL
Drop sequence statement is used to remove a sequence object from the database.
DROP SEQUENCE user_id_seq;
UNIQUE constraint
Ensures all values in a column or combination are distinct
CREATE TABLE users (email TEXT UNIQUE);
DROP-TYPE DDL
Drop type statement is used to remove a user-defined type from the database.
DROP TYPE gender;
NOT NULL constraint
Prevents NULL values from being stored in a column
CREATE TABLE users (name TEXT NOT NULL, email TEXT NOT NULL);
COMMIT DML
Commit statement is used to end a transaction and make all changes permanent.
COMMIT;
ROLLBACK DML
Rollback statement is used to undo all changes made during a transaction and revert the database to the state it was in before the transaction began.
ROLLBACK;
SAVEPOINT DML
Savepoint statement is used to create a savepoint within a transaction, which can be used to roll back part of a transaction.
SAVEPOINT my_savepoint;