SQL and databases: glossary
The query-language terms analysts meet daily: joins, views, window functions, transactions and database safety.
- Inner JoinAn inner join combines rows from two tables and keeps only the rows that have a match in both.
- Left JoinA left join keeps every row from the first (left) table and attaches matching rows from the second table, with nulls where nothing matches.
- SubqueryA subquery is a query nested inside another query, used to compute a value or a set of rows that the outer query then uses.
- CTE (Common Table Expression)A CTE is a named temporary result defined at the start of a query with WITH, which later parts of the query can refer to like a table.
- Window FunctionA window function calculates a value across a set of related rows, such as a running total or a rank, without collapsing those rows into one.
- Aggregate FunctionAn aggregate function summarises many rows into one value, such as COUNT, SUM, AVG, MIN or MAX.
- GROUP BYGROUP BY splits rows into groups that share a value, so an aggregate function can be calculated separately for each group.
- Database ViewA view is a saved query that behaves like a table, so you can reuse complex logic and control which columns people can see.
- Database IndexA database index is a lookup structure on one or more columns that lets the database find matching rows without reading the whole table.
- SQL InjectionSQL injection is an attack where untrusted input is run as part of a database query, letting an attacker read or change data they should not reach.
- Read-Only Database UserA read-only database user is an account that can run queries to read data but cannot insert, change or delete anything.
- ACID TransactionsACID is a set of four guarantees, atomicity, consistency, isolation and durability, that make database transactions reliable.