What is the difference between a Primary Key and a Foreign Key?

A Primary Key is a unique identifier for a specific row within its own table (e.g., CustomerID in a Customers table). A Foreign Key is a column that provides a link between data in two tables by referencing the Primary Key of another table (e.g., CustomerID inside an Orders table). This relationship is what makes a database "relational."

When should I use a LEFT JOIN instead of an INNER JOIN?

INNER JOIN: Returns only the rows where there is a match in both tables. If an order doesn't have a linked customer, it won't show up. LEFT JOIN: Returns all rows from the left table, even if there are no matches in the right table. This is essential when you want to find "missing" data, such as a list of all customers, including those who haven't placed an order yet.

What is the difference between WHERE and HAVING?

Both are used to filter data, but they happen at different stages: WHERE filters individual rows before any grouping or calculation happens. HAVING filters grouped data after an aggregate function (like SUM or COUNT ) has been applied. You cannot use WHERE to filter on a SUM(Sales) column; you must use HAVING .

What are "Aggregates" in SQL?

Aggregates are functions that perform a calculation on a set of values to return a single result. The most common are: COUNT() : Number of rows. SUM() : Total value of a numeric column. AVG() : The mathematical mean. MIN() / MAX() : The lowest and highest values in a set.

What is a CTE (Common Table Expression), and why use it?

A CTE is a temporary result set that you define at the start of your query using the WITH keyword. Think of it as a "virtual table" that only exists for that specific query. They are widely preferred over subqueries because they make complex SQL much easier to read, organize, and maintain.

Understanding SQL: A Glossary of Essential Terms

Clear Definitions of Key SQL Terms

Explore the SQL glossary below. Each term is listed individually for easy reference and future expansion with examples or best practices.

1. Core Concepts

SQL (Structured Query Language)

A standard language used to manage and manipulate relational databases. It enables querying, updating, and structuring data.

Relational Database

A database that organizes data into tables with rows and columns, using relationships between tables via keys.

Schema

The structure that defines tables, columns, data types, and relationships within a database.

Primary Key

A unique identifier for each row in a table. Ensures data integrity and enables relationships.

Foreign Key

A column that links one table to another by referencing the primary key of the related table.

Index

A performance optimization structure that speeds up data retrieval by indexing specific columns.

2. SQL Syntax & Clauses

SELECT

Retrieves data from one or more tables.

FROM

Specifies the table(s) to query.

WHERE

Filters rows based on specified conditions.

GROUP BY

Aggregates rows that share a common value in specified columns.

HAVING

Filters grouped results based on aggregate conditions.

ORDER BY

Sorts query results in ascending (ASC) or descending (DESC) order.

LIMIT / TOP

Restricts the number of rows returned by a query.

DISTINCT

Removes duplicate rows from the result set

AS (Alias)

Renames columns or tables temporarily for readability.

3. Data Types & Operators

VARCHAR / CHAR

Text data types. VARCHAR allows variable-length strings; CHAR is fixed-length.

INT / FLOAT / DECIMAL

Numeric types for whole numbers, floating-point values, and precise decimals.

DATE / TIME / DATETIME

Temporal data types for storing calendar and clock values.

NULL

Represents missing or undefined data.

Comparison Operators

Used in filtering and conditional logic, e.g. =, <>, >, <, >=, <=

BETWEEN / IN / LIKE

Pattern and range matching operators for flexible filtering

4. Data Manipulation

INSERT

Adds new rows to a table.

UPDATE

Modifies existing data in a table.

DELETE

Removes rows from a table

MERGE

Combines INSERT, UPDATE, and DELETE logic to synchronize data.

TRUNCATE

Removes all rows from a table quickly, without logging individual deletions.

5. Data Definition & Structure

CREATE TABLE

Defines a new table and its columns.

ALTER TABLE

Modifies an existing table’s structure (e.g., add/remove columns).

DROP TABLE

Deletes a table and its data permanently.

CREATE INDEX

Builds an index to improve query performance.

VIEW

A virtual table based on a SQL query. Does not store data itself.

6. Advanced SQL Features

JOIN

Combines rows from two or more tables based on related columns. Types include INNER, LEFT, RIGHT, and FULL OUTER.

UNION / UNION ALL

Combines results from multiple queries. UNION removes duplicates; UNION ALL retains them.

Subquery

A query nested inside another query. Used for filtering, calculations, or conditional logic.

Common Table Expression (CTE)

Temporary named result set defined using WITH. Improves readability and modularity.

Window Function

Performs calculations across a set of rows related to the current row (e.g., RANK(), ROW_NUMBER()).

Stored Procedure

A saved block of SQL code that can be executed repeatedly with parameters.

Trigger

A procedure that automatically executes in response to specific events on a table (e.g., INSERT, UPDATE).

7. Access & Security

GRANT / REVOKE

Assigns or removes user permissions for database actions.

Role

A group of permissions assigned to users for simplified access control.

Transaction

A sequence of operations performed as a single unit. Ensures consistency via BEGIN, COMMIT, and ROLLBACK.

ACID Properties

Guarantees reliable transactions: Atomicity, Consistency, Isolation, Durability.

Get Involved:
Suggest Terms & Request Clarification

Help Us Make the Glossary Even Better

We’re committed to keeping our glossary comprehensive and up to date, but we know there’s always room for improvement. If you’ve come across a term that’s missing or need further explanation on any topic, we’d love to hear from you.

Suggest a New Term

Have a technical phrase or concept you think should be added? Let us know! Your input helps us ensure the glossary remains useful to everyone in the community.

Request Clarification

If you find any definition unclear or would like more examples, feel free to request clarification. We aim to provide clear, practical insights for all users.

Share Your Feedback

Email your suggestions directly
Fill in our feedback form

Thank you for helping us build a better resource for everyone!

SQL Glossary– Frequently Asked Questions

What is the difference between a Primary Key and a Foreign Key?
A Primary Key is a unique identifier for a specific row within its own table (e.g., CustomerID in a Customers table). A Foreign Key is a column that provides a link between data in two tables by referencing the Primary Key of another table (e.g., CustomerID inside an Orders table). This relationship is what makes a database "relational."
When should I use a LEFT JOIN instead of an INNER JOIN?
- INNER JOIN: Returns only the rows where there is a match in both tables. If an order doesn't have a linked customer, it won't show up.
- LEFT JOIN: Returns all rows from the left table, even if there are no matches in the right table. This is essential when you want to find "missing" data, such as a list of all customers, including those who haven't placed an order yet.
What is the difference between WHERE and HAVING?
Both are used to filter data, but they happen at different stages:
- WHERE filters individual rows before any grouping or calculation happens.
- HAVING filters grouped data after an aggregate function (like SUM or COUNT) has been applied. You cannot use WHERE to filter on a SUM(Sales) column; you must use HAVING.
What are "Aggregates" in SQL?
Aggregates are functions that perform a calculation on a set of values to return a single result. The most common are:
- COUNT(): Number of rows.
- SUM(): Total value of a numeric column.
- AVG(): The mathematical mean.
- MIN() / MAX(): The lowest and highest values in a set.
What is a CTE (Common Table Expression), and why use it?
A CTE is a temporary result set that you define at the start of your query using the WITH keyword. Think of it as a "virtual table" that only exists for that specific query. They are widely preferred over subqueries because they make complex SQL much easier to read, organize, and maintain.

Other SQL Resources

You can read all of our SQL blog archives here

SQL Blog Archives – Select Distinct