devhexlab

SQL Reference

Searchable SQL keyword and function reference with syntax examples

sqlreferencedatabasequerykeywords

Showing 65 of 65 entries across 13 sections

SELECT

SELECT

Retrieves rows from one or more columns in a table.

Syntax
SELECT column1, column2
FROM table_name;
Example
SELECT first_name, last_name
FROM employees;

SELECT DISTINCT

Returns only unique (non-duplicate) values.

Syntax
SELECT DISTINCT column1
FROM table_name;
Example
SELECT DISTINCT department
FROM employees;

SELECT TOP / LIMIT

Restricts the number of rows returned.

Syntax
-- SQL Server
SELECT TOP 10 * FROM table_name;

-- MySQL / PostgreSQL
SELECT * FROM table_name
LIMIT 10;
Example
SELECT TOP 5 name, salary
FROM employees
ORDER BY salary DESC;

SELECT INTO

Copies rows from one table into a new table.

Syntax
SELECT column1, column2
INTO new_table
FROM source_table
WHERE condition;
Example
SELECT * INTO employees_backup
FROM employees
WHERE active = 1;

WHERE

Filters rows based on a condition. Supports =, <>, <, >, <=, >=, BETWEEN, LIKE, IN, IS NULL, AND, OR, NOT.

Syntax
SELECT * FROM table_name
WHERE condition;
Example
SELECT * FROM employees
WHERE department = 'Engineering'
  AND salary > 80000;

ORDER BY

Sorts the result set. ASC (default) is ascending; DESC is descending.

Syntax
SELECT * FROM table_name
ORDER BY column1 ASC, column2 DESC;
Example
SELECT name, salary
FROM employees
ORDER BY salary DESC, name ASC;

GROUP BY

Groups rows that share the same values in specified columns, used with aggregate functions.

Syntax
SELECT column, AGG_FUNC(col)
FROM table_name
GROUP BY column;
Example
SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department;

HAVING

Filters groups after GROUP BY (like WHERE but for aggregates).

Syntax
SELECT column, AGG_FUNC(col)
FROM table_name
GROUP BY column
HAVING condition;
Example
SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

UNION

Combines results of two queries and removes duplicates. Use UNION ALL to keep duplicates.

Syntax
SELECT columns FROM table1
UNION
SELECT columns FROM table2;
Example
SELECT name FROM customers
UNION
SELECT name FROM prospects;

INTERSECT

Returns rows that appear in both result sets.

Syntax
SELECT columns FROM table1
INTERSECT
SELECT columns FROM table2;
Example
SELECT email FROM newsletter_subscribers
INTERSECT
SELECT email FROM customers;

EXCEPT / MINUS

Returns rows in the first query that are not in the second.

Syntax
-- Standard SQL / SQL Server / PostgreSQL
SELECT columns FROM table1
EXCEPT
SELECT columns FROM table2;

-- Oracle
SELECT columns FROM table1
MINUS
SELECT columns FROM table2;
Example
SELECT email FROM all_users
EXCEPT
SELECT email FROM unsubscribed;

INSERT

INSERT INTO

Adds one or more new rows to a table.

Syntax
INSERT INTO table_name (column1, column2)
VALUES (value1, value2);
Example
INSERT INTO employees (first_name, last_name, department, salary)
VALUES ('Alice', 'Smith', 'Engineering', 95000);

INSERT INTO SELECT

Copies rows from one table and inserts them into another.

Syntax
INSERT INTO table_name (column1, column2)
SELECT column1, column2
FROM source_table
WHERE condition;
Example
INSERT INTO employees_archive (id, name, department)
SELECT id, name, department
FROM employees
WHERE termination_date IS NOT NULL;

UPDATE

UPDATE

Modifies existing rows. Always include a WHERE clause to avoid updating all rows.

Syntax
UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;
Example
UPDATE employees
SET salary = salary * 1.10,
    title = 'Senior Engineer'
WHERE department = 'Engineering'
  AND years_experience >= 5;

DELETE

DELETE

Removes rows from a table. Always include WHERE to avoid deleting all rows. Can be rolled back.

Syntax
DELETE FROM table_name
WHERE condition;
Example
DELETE FROM employees
WHERE termination_date < '2020-01-01';

TRUNCATE

Removes all rows quickly without logging individual row deletions. Cannot be rolled back in most databases.

Syntax
TRUNCATE TABLE table_name;
Example
TRUNCATE TABLE session_logs;

CREATE

CREATE TABLE

Creates a new table with defined columns and data types.

Syntax
CREATE TABLE table_name (
  column1 datatype CONSTRAINT,
  column2 datatype,
  ...
);
Example
CREATE TABLE employees (
  id        INT          PRIMARY KEY AUTO_INCREMENT,
  first_name VARCHAR(50)  NOT NULL,
  last_name  VARCHAR(50)  NOT NULL,
  email      VARCHAR(100) UNIQUE,
  salary     DECIMAL(10,2) DEFAULT 0,
  hired_at   DATE
);

CREATE DATABASE

Creates a new database.

Syntax
CREATE DATABASE database_name;
Example
CREATE DATABASE company_db;

CREATE INDEX

Creates an index to speed up queries. UNIQUE prevents duplicate values.

Syntax
CREATE [UNIQUE] INDEX index_name
ON table_name (column1, column2);
Example
CREATE INDEX idx_employees_email
ON employees (email);

CREATE UNIQUE INDEX idx_orders_ref
ON orders (reference_number);

CREATE VIEW

Creates a virtual table based on a saved SELECT statement.

Syntax
CREATE VIEW view_name AS
SELECT columns
FROM table_name
WHERE condition;
Example
CREATE VIEW engineering_team AS
SELECT id, first_name, last_name, salary
FROM employees
WHERE department = 'Engineering';

ALTER

ALTER TABLE: ADD COLUMN

Adds a new column to an existing table.

Syntax
ALTER TABLE table_name
ADD column_name datatype;
Example
ALTER TABLE employees
ADD phone VARCHAR(20);

ALTER TABLE: DROP COLUMN

Removes a column from an existing table.

Syntax
ALTER TABLE table_name
DROP COLUMN column_name;
Example
ALTER TABLE employees
DROP COLUMN fax_number;

ALTER TABLE: MODIFY / ALTER COLUMN

Changes the data type or constraints of an existing column.

Syntax
-- MySQL
ALTER TABLE table_name
MODIFY COLUMN column_name new_datatype;

-- SQL Server / PostgreSQL
ALTER TABLE table_name
ALTER COLUMN column_name new_datatype;
Example
ALTER TABLE employees
MODIFY COLUMN salary DECIMAL(12, 2) NOT NULL;

ALTER TABLE: RENAME

Renames an existing table.

Syntax
-- MySQL
ALTER TABLE old_name
RENAME TO new_name;

-- SQL Server
EXEC sp_rename 'old_name', 'new_name';
Example
ALTER TABLE staff
RENAME TO employees;

DROP

DROP TABLE

Permanently removes a table and all its data. IF EXISTS prevents an error if the table doesn't exist.

Syntax
DROP TABLE [IF EXISTS] table_name;
Example
DROP TABLE IF EXISTS temp_imports;

DROP DATABASE

Permanently removes a database and all its tables and data.

Syntax
DROP DATABASE [IF EXISTS] database_name;
Example
DROP DATABASE IF EXISTS legacy_db;

DROP INDEX

Removes an existing index.

Syntax
-- MySQL
DROP INDEX index_name ON table_name;

-- SQL Server / PostgreSQL
DROP INDEX index_name;
Example
DROP INDEX idx_employees_email ON employees;

DROP VIEW

Removes an existing view.

Syntax
DROP VIEW [IF EXISTS] view_name;
Example
DROP VIEW IF EXISTS engineering_team;

JOINs

INNER JOIN

Returns rows where there is a match in both tables.

Syntax
SELECT a.col, b.col
FROM table_a a
INNER JOIN table_b b ON a.id = b.a_id;
Example
SELECT e.first_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

LEFT JOIN

Returns all rows from the left table, and matching rows from the right. Non-matching right-side columns are NULL.

Syntax
SELECT a.col, b.col
FROM table_a a
LEFT JOIN table_b b ON a.id = b.a_id;
Example
SELECT e.first_name, m.first_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

RIGHT JOIN

Returns all rows from the right table, and matching rows from the left. Non-matching left-side columns are NULL.

Syntax
SELECT a.col, b.col
FROM table_a a
RIGHT JOIN table_b b ON a.id = b.a_id;
Example
SELECT e.first_name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;

FULL OUTER JOIN

Returns all rows from both tables. Columns are NULL where no match exists.

Syntax
SELECT a.col, b.col
FROM table_a a
FULL OUTER JOIN table_b b ON a.id = b.a_id;
Example
SELECT c.name, o.order_date
FROM customers c
FULL OUTER JOIN orders o ON c.id = o.customer_id;

CROSS JOIN

Returns the Cartesian product of both tables: every row in A combined with every row in B.

Syntax
SELECT * FROM table_a
CROSS JOIN table_b;
Example
SELECT s.size, c.color
FROM sizes s
CROSS JOIN colors c;

SELF JOIN

Joins a table to itself using aliases, useful for hierarchical data.

Syntax
SELECT a.col, b.col
FROM table_name a
JOIN table_name b ON a.related_id = b.id;
Example
SELECT e.first_name AS employee,
       m.first_name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.id;

Aggregate Functions

COUNT

Returns the number of rows. COUNT(*) counts all rows; COUNT(col) ignores NULLs.

Syntax
COUNT(*) or COUNT(column_name)
Example
SELECT department, COUNT(*) AS total
FROM employees
GROUP BY department;

SUM

Returns the total sum of a numeric column.

Syntax
SUM(column_name)
Example
SELECT department, SUM(salary) AS total_payroll
FROM employees
GROUP BY department;

AVG

Returns the average value of a numeric column.

Syntax
AVG(column_name)
Example
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

MIN

Returns the smallest value in a column.

Syntax
MIN(column_name)
Example
SELECT MIN(salary) AS lowest_salary
FROM employees
WHERE department = 'Engineering';

MAX

Returns the largest value in a column.

Syntax
MAX(column_name)
Example
SELECT department, MAX(salary) AS top_salary
FROM employees
GROUP BY department;

ROUND

Rounds a number to the specified number of decimal places.

Syntax
ROUND(number, decimal_places)
Example
SELECT first_name,
       ROUND(salary / 12, 2) AS monthly_salary
FROM employees;

COALESCE

Returns the first non-NULL value in the list. Useful for providing defaults.

Syntax
COALESCE(value1, value2, ..., fallback)
Example
SELECT first_name,
       COALESCE(phone, mobile, 'No contact') AS contact
FROM employees;

NULLIF

Returns NULL if both expressions are equal; otherwise returns expression1.

Syntax
NULLIF(expression1, expression2)
Example
-- Avoid division by zero
SELECT total_sales / NULLIF(num_transactions, 0)
FROM daily_stats;

String Functions

UPPER

Converts a string to all uppercase letters.

Syntax
UPPER(string)
Example
SELECT UPPER(first_name) FROM employees;
-- 'alice' → 'ALICE'

LOWER

Converts a string to all lowercase letters.

Syntax
LOWER(string)
Example
SELECT LOWER(email) FROM employees;
-- 'Alice@Example.COM' → 'alice@example.com'

LEN / LENGTH

Returns the number of characters in a string.

Syntax
-- SQL Server
LEN(string)

-- MySQL / PostgreSQL
LENGTH(string)
Example
SELECT first_name, LEN(first_name) AS name_length
FROM employees;

SUBSTRING

Extracts a portion of a string starting at start_pos for length characters.

Syntax
SUBSTRING(string, start_pos, length)
-- or SUBSTR(string, start, length)
Example
SELECT SUBSTRING(phone, 1, 3) AS area_code
FROM employees;
-- '555-123-4567' → '555'

CONCAT

Joins two or more strings together.

Syntax
CONCAT(string1, string2, ...)
-- or string1 || string2  (PostgreSQL / SQLite)
Example
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;

TRIM

Removes leading and/or trailing whitespace (or specified characters) from a string.

Syntax
TRIM([LEADING | TRAILING | BOTH] FROM string)
-- or TRIM(string)
Example
SELECT TRIM(email) AS clean_email
FROM employees;
-- '  alice@example.com  ' → 'alice@example.com'

REPLACE

Replaces all occurrences of a substring within a string.

Syntax
REPLACE(string, old_substring, new_substring)
Example
SELECT REPLACE(phone, '-', '') AS phone_digits
FROM employees;
-- '555-123-4567' → '5551234567'

LIKE

Pattern matching operator. % matches any sequence; _ matches one character. Use NOT LIKE for inverse.

Syntax
column LIKE pattern
-- % = any sequence of characters
-- _ = exactly one character
Example
SELECT * FROM employees
WHERE email LIKE '%@example.com';

SELECT * FROM employees
WHERE first_name LIKE 'A_i%';

Date Functions

NOW()

Returns the current date and time.

Syntax
NOW() -- or GETDATE() (SQL Server) / CURRENT_TIMESTAMP
Example
SELECT NOW() AS current_datetime;
-- 2024-03-15 14:30:00

CURDATE / CURRENT_DATE

Returns the current date without the time component.

Syntax
CURDATE()            -- MySQL
CURRENT_DATE         -- PostgreSQL / Standard SQL
CAST(GETDATE() AS DATE)  -- SQL Server
Example
SELECT * FROM orders
WHERE order_date = CURDATE();

DATEADD

Adds a time interval to a date.

Syntax
-- SQL Server
DATEADD(interval, number, date)

-- MySQL
DATE_ADD(date, INTERVAL number unit)

-- PostgreSQL
date + INTERVAL 'n unit'
Example
-- SQL Server
SELECT DATEADD(day, 30, order_date) AS due_date
FROM orders;

-- MySQL
SELECT DATE_ADD(order_date, INTERVAL 30 DAY) AS due_date
FROM orders;

DATEDIFF

Returns the number of days between two dates.

Syntax
-- SQL Server / MySQL
DATEDIFF(end_date, start_date)

-- PostgreSQL
end_date - start_date
Example
SELECT DATEDIFF(NOW(), hired_at) AS days_employed
FROM employees;

DATE_FORMAT / FORMAT

Formats a date value as a string using format patterns.

Syntax
-- MySQL
DATE_FORMAT(date, format_string)

-- SQL Server
FORMAT(date, format_string)

-- PostgreSQL
TO_CHAR(date, format_string)
Example
-- MySQL
SELECT DATE_FORMAT(hired_at, '%d %M %Y') AS hire_date
FROM employees;
-- '15 March 2024'

Constraints

PRIMARY KEY

Uniquely identifies each row. Cannot be NULL. A table can have only one primary key (which may span multiple columns).

Syntax
-- Inline
column_name datatype PRIMARY KEY

-- Table-level
PRIMARY KEY (col1, col2)
Example
CREATE TABLE orders (
  order_id   INT         PRIMARY KEY AUTO_INCREMENT,
  order_date DATE        NOT NULL
);

FOREIGN KEY

Enforces referential integrity between two tables. Options: CASCADE, SET NULL, RESTRICT, NO ACTION.

Syntax
FOREIGN KEY (column_name)
REFERENCES other_table (column_name)
  ON DELETE CASCADE
  ON UPDATE CASCADE
Example
CREATE TABLE orders (
  order_id    INT PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id)
    REFERENCES customers (id)
    ON DELETE CASCADE
);

UNIQUE

Ensures all values in a column (or combination of columns) are distinct.

Syntax
column_name datatype UNIQUE
-- or UNIQUE (col1, col2) at table level
Example
CREATE TABLE users (
  id    INT  PRIMARY KEY,
  email TEXT UNIQUE
);

NOT NULL

Prevents NULL values from being stored in a column.

Syntax
column_name datatype NOT NULL
Example
CREATE TABLE products (
  id    INT          PRIMARY KEY,
  name  VARCHAR(100) NOT NULL,
  price DECIMAL(10,2) NOT NULL
);

CHECK

Ensures that values in a column satisfy a specified condition.

Syntax
CHECK (condition)
Example
CREATE TABLE employees (
  id     INT PRIMARY KEY,
  salary DECIMAL(10,2) CHECK (salary >= 0),
  age    INT CHECK (age BETWEEN 16 AND 120)
);

DEFAULT

Provides a default value inserted when no value is specified.

Syntax
column_name datatype DEFAULT default_value
Example
CREATE TABLE orders (
  id         INT PRIMARY KEY,
  status     VARCHAR(20)  DEFAULT 'pending',
  created_at TIMESTAMP    DEFAULT CURRENT_TIMESTAMP
);

Transactions

BEGIN / START TRANSACTION

Marks the start of an explicit transaction. All subsequent statements are part of the transaction until COMMIT or ROLLBACK.

Syntax
-- SQL Server
BEGIN TRANSACTION;

-- MySQL / PostgreSQL
START TRANSACTION;
Example
START TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

COMMIT

Permanently saves all changes made within the current transaction.

Syntax
COMMIT;
Example
START TRANSACTION;
INSERT INTO orders (customer_id, total) VALUES (42, 199.99);
COMMIT;

ROLLBACK

Undoes all changes made in the current transaction (or back to a named savepoint).

Syntax
ROLLBACK;
-- or
ROLLBACK TO SAVEPOINT savepoint_name;
Example
START TRANSACTION;
DELETE FROM orders WHERE id = 99;
-- Something went wrong
ROLLBACK;

SAVEPOINT

Creates a named point within a transaction you can roll back to without aborting the entire transaction.

Syntax
SAVEPOINT savepoint_name;
-- later:
ROLLBACK TO SAVEPOINT savepoint_name;
RELEASE SAVEPOINT savepoint_name;
Example
START TRANSACTION;
INSERT INTO log (msg) VALUES ('start');
SAVEPOINT after_log;
DELETE FROM temp_data;
-- Undo only the delete:
ROLLBACK TO SAVEPOINT after_log;
COMMIT;

Related Tools

Git Commands Cheatsheet

Searchable reference of essential Git commands with examples

Regex Cheatsheet

Interactive regex cheatsheet where you can click patterns to test them live

JSON Formatter

Beautify and format JSON with customizable indentation