๐๏ธ SQL Dictionary
Every keyword, operator, and symbol โ with plain-English explanations.
DML SQL CommandsData Manipulation Language commands are used to add, delete, update, and retrieve data from a database.
SELECT * FROM users;
INSERT-INTO SQL CommandsData 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 dmlRetrieves rows from one or more tables; the core read statement in SQL
SELECT id, name FROM users WHERE active = true;
INSERT dmlAdds one or more rows into a table
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');VALUES SQL CommandsData Manipulation Language commands are used to add, delete, update, and retrieve data from a database.
VALUES ('John Doe', 'john@example.com');DEFAULT SQL CommandsData Manipulation Language commands are used to add, delete, update, and retrieve data from a database.
DEFAULT VALUES;
ON-CONFLICT-DO-NOTHING SQL CommandsData Manipulation Language commands are used to add, delete, update, and retrieve data from a database.
ON CONFLICT (name) DO NOTHING;
CREATE TABLE ddlDefines 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 CommandsData 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 ddlModifies an existing table: add/drop columns, rename, change types, add constraints
ALTER TABLE users ADD COLUMN phone TEXT;
RETURNING SQL CommandsData Manipulation Language commands are used to add, delete, update, and retrieve data from a database.
RETURNING name, email;
UPDATE SQL CommandsData 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 ddlCreates an index on one or more columns to speed up query lookups
CREATE INDEX idx_users_email ON users (email);
SET SQL CommandsData Manipulation Language commands are used to add, delete, update, and retrieve data from a database.
SET email = 'newemail@example.com';
FROM SQL CommandsData Manipulation Language commands are used to add, delete, update, and retrieve data from a database.
FROM users;
WHERE clauseFilters rows based on a condition; applied before grouping
SELECT * FROM orders WHERE total > 100 AND status = 'paid';
DELETE SQL CommandsData Manipulation Language commands are used to add, delete, update, and retrieve data from a database.
DELETE FROM users WHERE name = 'John Doe';
JOIN clauseCombines 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 clauseReturns 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 CommandsData Manipulation Language commands are used to add, delete, update, and retrieve data from a database.
USING orders;
GROUP BY clauseGroups rows with the same values into summary rows; used with aggregate functions
SELECT status, COUNT(*) FROM orders GROUP BY status;
TRUNCATE-TABLE SQL CommandsData Manipulation Language commands are used to add, delete, update, and retrieve data from a database.
TRUNCATE TABLE users;
CASCADE SQL CommandsData Manipulation Language commands are used to add, delete, update, and retrieve data from a database.
TRUNCATE TABLE users CASCADE;
HAVING clauseFilters 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 clauseSorts 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 CommandsData Manipulation Language commands are used to add, delete, update, and retrieve data from a database.
RESTART IDENTITY;
LIMIT clauseRestricts the number of rows returned by a query
SELECT * FROM logs ORDER BY created_at DESC LIMIT 100;
MERGE-INTO SQL CommandsData 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 JoinsInner 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 clauseSkips a specified number of rows before beginning to return rows
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 40;
DISTINCT keywordEliminates duplicate rows from the result set
SELECT DISTINCT country FROM users;
LEFT-JOIN JoinsLeft 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 aggregateReturns the number of rows matching a condition; COUNT(*) counts all rows
SELECT COUNT(*) FROM users;
LEFT-OUTER-JOIN JoinsLeft 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 JoinsRight 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 aggregateReturns the total sum of a numeric column
SELECT SUM(total) FROM orders WHERE status = 'paid';
AVG aggregateReturns the average value of a numeric column
SELECT AVG(score) FROM test_results;
RIGHT-OUTER-JOIN JoinsRight 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 JoinsFull 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 aggregateReturns the minimum value of a column
SELECT MIN(price) FROM products WHERE category = 'books';
CROSS-JOIN JoinsCross 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 aggregateReturns the maximum value of a column
SELECT MAX(created_at) FROM posts WHERE user_id = 42;
COALESCE functionReturns the first non-NULL value from a list of expressions
SELECT COALESCE(phone, email, 'no contact') FROM users;
NATURAL-JOIN JoinsNatural 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 JoinsJoin 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 functionReturns NULL if two expressions are equal, otherwise returns the first expression
SELECT NULLIF(score, 0) FROM results;
CASE expressionEvaluates 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 JoinsSelf 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 DDLCreate 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 operatorTests whether a value matches any value in a list or subquery
SELECT * FROM users WHERE role IN ('admin','moderator');CREATE-TABLE-IF-NOT-EXISTS DDLCreate 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 DDLCreate 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 operatorPattern matching with % (any chars) and _ (single char)
SELECT * FROM products WHERE name LIKE 'Apple%';
CREATE-INDEX DDLCreate index statement is used to create a new index on a table to improve query performance.
CREATE INDEX idx_name ON users (name);
ILIKE operatorCase-insensitive version of LIKE (PostgreSQL)
SELECT * FROM posts WHERE title ILIKE '%javascript%';
BETWEEN operatorTests whether a value is within a range (inclusive of both endpoints)
SELECT * FROM orders WHERE total BETWEEN 100 AND 500;
CREATE-UNIQUE-INDEX DDLCreate 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 DDLCreate 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 operatorReturns true if a subquery returns any rows
SELECT * FROM users WHERE EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id);
CREATE-VIEW DDLCreate 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-opCombines results of two queries, removing duplicates; UNION ALL keeps duplicates
SELECT name FROM customers UNION SELECT name FROM suppliers;
CREATE-MATERIALIZED-VIEW DDLCreate 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 cteDefines 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 DDLCreate 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 DDLCreate 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-fnAssigns 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 DDLCreate type as set statement is used to create a new set type in the database.
CREATE TYPE tags AS SET OF VARCHAR;
RANK window-fnAssigns 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 DDLCreate type as array statement is used to create a new array type in the database.
CREATE TYPE tags_array AS ARRAY [VARCHAR];
ALTER-TABLE DDLAlter table statement is used to modify the structure of an existing table.
ALTER TABLE users ADD COLUMN age INTEGER;
DROP-TABLE DDLDrop table statement is used to remove a table from the database.
DROP TABLE users;
DROP-INDEX DDLDrop index statement is used to remove an index from a table.
DROP INDEX idx_name;
PRIMARY KEY constraintUniquely identifies each row in a table; implicitly creates a unique index
CREATE TABLE users (id SERIAL PRIMARY KEY, email TEXT);
DROP-VIEW DDLDrop view statement is used to remove a view from the database.
DROP VIEW user_summary;
FOREIGN KEY constraintEnforces referential integrity between tables
CREATE TABLE orders (id SERIAL PRIMARY KEY, user_id INT REFERENCES users(id) ON CASCADE);
DROP-SEQUENCE DDLDrop sequence statement is used to remove a sequence object from the database.
DROP SEQUENCE user_id_seq;
UNIQUE constraintEnsures all values in a column or combination are distinct
CREATE TABLE users (email TEXT UNIQUE);
DROP-TYPE DDLDrop type statement is used to remove a user-defined type from the database.
DROP TYPE gender;
NOT NULL constraintPrevents NULL values from being stored in a column
CREATE TABLE users (name TEXT NOT NULL, email TEXT NOT NULL);
COMMIT DMLCommit statement is used to end a transaction and make all changes permanent.
COMMIT;
ROLLBACK DMLRollback 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 DMLSavepoint statement is used to create a savepoint within a transaction, which can be used to roll back part of a transaction.
SAVEPOINT my_savepoint;