What Is SQL? The Relational Model, the ANSI/ISO SQL Standard and the T-SQL Dialect

Version note: The SQL (Structured Query Language) queries in this post were run on SQL Server 2025 (17.0, Cumulative Update 10); the dialect comparisons were run on PostgreSQL 18.6 and MySQL 8.4.

Guess First: How Many of the 77 Books Lack This Subtitle?

The Bookstore catalog holds 77 books. Only one of them, Design Patterns, carries the subtitle "Elements of Reusable Object-Oriented Software". Before you run the next query, name a number: how many books do not have that subtitle?

SELECT COUNT(*) AS total_books,
       (SELECT COUNT(*) FROM dbo.books WHERE subtitle <> N'Elements of Reusable Object-Oriented Software') AS not_that_subtitle
FROM dbo.books;

Someone who thinks in C# answers 76 straight away: one of the 77 books is equal, the other 76 are not. The real output:

total_books  not_that_subtitle
-----------  -----------------
77           5

(1 rows affected)

Five. Seventy-one books have no subtitle; that column holds NULL, and the comparison NULL <> 'x' is neither true nor false but "unknown". Since WHERE only lets true rows through, those 71 books were silently dropped. We will take three-valued logic apart in the 6th post of the series. This post is about something more fundamental: the places where SQL forces you to think differently than C#, the relational model behind that difference, and who owns the language we call "SQL", which standard defines it and which dialects it lives in.

The Relational Model: Tables, Rows, Columns and Keys

SQL is not a database product; it is a language. The idea behind it comes from "A Relational Model of Data for Large Shared Data Banks", the paper Edgar F. Codd published in Communications of the ACM (Association for Computing Machinery) in 1970 while working at IBM (International Business Machines). Codd's proposal was this: model data not as records chained together with pointers, but as mathematical relations, that is, sets of tuples, and define querying as operations over those sets. In SQL a relation is called a table, a tuple a row and an attribute a column.

The most portable way to see a table's structure is the INFORMATION_SCHEMA views defined by the standard. The same query runs on SQL Server, PostgreSQL and MySQL:

SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'books'
ORDER BY ORDINAL_POSITION;

COLUMN_NAME     DATA_TYPE  CHARACTER_MAXIMUM_LENGTH  IS_NULLABLE
--------------  ---------  ------------------------  -----------
book_id         int        NULL                      NO
isbn            char       13                        NO
title           nvarchar   200                       NO
subtitle        nvarchar   200                       YES
category_id     int        NULL                      YES
language_code   char       2                         NO
published_year  smallint   NULL                      YES
page_count      smallint   NULL                      YES
list_price      decimal    NULL                      NO
listed_on       date       NULL                      NO
is_active       bit        NULL                      NO

(11 rows affected)

Three rules of the model can be read straight from this. Every column has exactly one type: list_price is decimal in every row; it cannot be text in one row and a number in another. Every column is either mandatory (NOT NULL) or allowed to have no value. And every row's identity is determined by a key:

SELECT tc.CONSTRAINT_NAME, tc.CONSTRAINT_TYPE, kcu.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS kcu
    ON kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
WHERE tc.TABLE_NAME = 'books'
ORDER BY tc.CONSTRAINT_TYPE, tc.CONSTRAINT_NAME;

CONSTRAINT_NAME      CONSTRAINT_TYPE  COLUMN_NAME
-------------------  ---------------  -----------
FK_books_categories  FOREIGN KEY      category_id
PK_books             PRIMARY KEY      book_id
UQ_books_isbn        UNIQUE           isbn

(3 rows affected)

book_id is the primary key: on its own it identifies every book. isbn is unique too; relational theory calls it a candidate key, a key that could have been the primary key but was not chosen. category_id is a foreign key: its value must be a category_id that exists in the categories table. Relationships between tables are built from values, not pointers. That is why combining two tables needs a join condition rather than a navigation property; we will cover joins in posts 8 and 9.

One nuance: Codd's relation is a mathematical set, so it has neither order nor duplicate elements. SQL departs from the theory here. You can insert the same row twice into a table without a key, and a SQL table actually behaves like a bag (a multiset that allows duplicates). On order, though, SQL stays faithful to the theory: a table has no order. Unless you write ORDER BY, the order of the result is not guaranteed. Seeing the same order every time on a small table does not make that order a contract; in post 5 we will see how this breaks pagination.

Thinking Set-Based: Sets Instead of Loops

A C# developer's reflex is the loop: for every category, find the books, count them, compute the average, add to a list. You can write that in SQL as well. T-SQL (Transact-SQL) offers variables, WHILE and table variables:

SET NOCOUNT ON;
DECLARE @result TABLE (category_id INT, book_count INT, avg_price DECIMAL(10,2));
DECLARE @id INT = (SELECT MIN(category_id) FROM dbo.categories);
SET STATISTICS IO ON;
WHILE @id IS NOT NULL
BEGIN
    /* one separate query per category */
    INSERT INTO @result (category_id, book_count, avg_price)
    SELECT @id, COUNT(*), AVG(list_price)
    FROM dbo.books
    WHERE category_id = @id;

    SET @id = (SELECT MIN(category_id) FROM dbo.categories WHERE category_id > @id);
END;
SET STATISTICS IO OFF;
SELECT category_id, book_count, avg_price FROM @result WHERE book_count > 0 ORDER BY category_id;

Asking the same question as a set is a single statement:

SET STATISTICS IO ON;
SELECT category_id, COUNT(*) AS book_count, AVG(list_price) AS avg_price
FROM dbo.books
WHERE category_id IS NOT NULL
GROUP BY category_id
ORDER BY category_id;
SET STATISTICS IO OFF;

category_id  book_count  avg_price
-----------  ----------  -----------
2            29          412.489655
3            9           551.533333
4            4           490.800000
5            6           350.466666
6            6           532.133333
9            5           432.380000
11           9           1092.222222
12           2           1300.000000
13           5           408.460000

(9 rows affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, ...
Table 'books'. Scan count 1, logical reads 3, physical reads 0, ...

The result is the same (only the number of decimals differs, because the loop version rounds the average to DECIMAL(10,2)). The real difference is in the SET STATISTICS IO output. We look at logical reads here, not time: the number of 8 KB pages the server read from memory, a measure that does not change from machine to machine. The set-based query scans books once and reads 3 pages. The loop produces these three lines again for each of the 13 categories (lines shortened):

Table '#BF9633CF'. Scan count 0, logical reads 1, ...
Table 'books'. Scan count 1, logical reads 3, ...
Table 'categories'. Scan count 1, logical reads 2, ...

13 iterations × (3 + 2 + 1) = 78 logical reads against 3 for the set-based query. On Bookstore's 77 books the difference is microseconds, but the ratio does not stay fixed: as the number of categories grows, the loop rescans the table every time. More importantly, because the loop dictates "how", it ties the optimizer's hands. Thinking set-based means asking "which set of rows do I want and which operation do I apply to it" instead of "what do I do for each row". Every post in this series exists to build that reflex.

A Declarative Language: You Write the "What", the Optimizer Picks the "How"

SQL is a declarative language. In the GROUP BY query above you did not say which index to use, whether rows should be grouped by hashing or by sorting, or which table to read first. You only described what the result should be. SQL Server's query optimizer makes those decisions: it looks at statistics, evaluates several candidate plans and picks the one it estimates to be cheapest.

This has two practical consequences. First, different spellings that produce the same result often end up as the same plan; before arguing about IN with a subquery versus a join, look at the plan. Second, the order you write is not the order of execution. SELECT sits at the start of the statement but is logically evaluated after FROM and WHERE. We will cover the logical query processing order in post 5 and reading execution plans in detail in the upcoming "SQL Server Derinlemesine" (SQL Server in Depth) series.

Statement Families: DDL, DML, DCL and TCL

SQL statements are traditionally split into four families: DDL (Data Definition Language) defines the schema, DML (Data Manipulation Language) reads and changes data, DCL (Data Control Language) manages permissions and TCL (Transaction Control Language) draws transaction boundaries. Some sources treat SELECT as a separate family called DQL (Data Query Language). The standard does not use these labels; its own classification is more detailed. But you will hear these four words all the time. All four together:

SET NOCOUNT ON;
/* DDL: a new table */
CREATE TABLE dbo.wishlist_items (
    customer_id INT NOT NULL REFERENCES dbo.customers (customer_id),
    book_id     INT NOT NULL REFERENCES dbo.books (book_id),
    added_at    DATETIME2(0) NOT NULL DEFAULT SYSUTCDATETIME(),
    PRIMARY KEY (customer_id, book_id)
);
/* DML: insert and read data */
INSERT INTO dbo.wishlist_items (customer_id, book_id) VALUES (1, 26), (1, 39);
SELECT customer_id, book_id FROM dbo.wishlist_items;
/* DCL: read-only permission */
CREATE USER sqlt_report_reader WITHOUT LOGIN;
GRANT SELECT ON dbo.wishlist_items TO sqlt_report_reader;
/* TCL: delete, look, undo */
BEGIN TRANSACTION;
    DELETE FROM dbo.wishlist_items WHERE customer_id = 1;
    SELECT COUNT(*) AS rows_inside_tran FROM dbo.wishlist_items;
ROLLBACK TRANSACTION;
SELECT COUNT(*) AS rows_after_rollback FROM dbo.wishlist_items;

customer_id  book_id
-----------  -------
1            26
1            39
rows_inside_tran
-------------------
0
rows_after_rollback
-------------------
2

We added Dune and The Hobbit to Ada's wishlist, deleted them inside a transaction and undid that with ROLLBACK. The SELECT inside the transaction saw zero rows; after ROLLBACK both rows came back. These four families are the skeleton of the series: DDL in posts 3 and 4, the reading side of DML from post 5 on, the writing side in post 15. The deep end of DCL and transactions (isolation, locks) belongs to the "SQL Server Derinlemesine" series.

A Short History of the Standard: From SQL-86 to SQL:2023

After Codd's paper, Donald D. Chamberlin and Raymond F. Boyce at IBM developed a language called SEQUEL (Structured English Query Language) for the System R prototype; a trademark conflict shortened the name to SQL. As the language spread, ANSI (American National Standards Institute) adopted it as a standard in 1986 and ISO (International Organization for Standardization) in 1987. Today the standard is called ISO/IEC 9075; IEC (International Electrotechnical Commission) stands for the joint committee with ISO. The standard is not a single document but a set of parts: the core of the language is Part 2 Foundation, the metadata views are Part 11 Schemata, and the newest is Part 16 PGQ (Property Graph Queries).

As the JSON rows show, the standard also serves to formalize, after the fact, what products already do. The Foundation part of SQL:2023 was published in June 2023 and is 1,715 pages on its own. The write-up by Peter Eisentraut, a PostgreSQL developer who takes part in the SQL standard work, lists the new features with their feature codes: GREATEST/LEAST is T054, UNIQUE NULLS DISTINCT is F292, and so on.

Those codes matter because of the least known fact about the standard: most features are optional. The standard defines a small mandatory core (Core SQL) and hundreds of optional features. When a product says "we support the SQL standard", it really means "most of the core plus these optional features". The standard even leaves some behaviour to the product on purpose; for example, until SQL:2023 whether a UNIQUE column may hold more than one NULL was implementation-defined, and SQL Server and PostgreSQL behave differently there (post 4). Markus Winand's Modern SQL site is a good source for tracking which feature works in which product and since which version.

Dialects: T-SQL, PostgreSQL and MySQL

Every product implements a subset of the standard together with its own extensions. The resulting language is called a dialect. SQL Server's dialect is T-SQL; it shares its roots with Sybase and offers variables, control flow such as IF/WHILE, and shortcuts such as TOP inside the same language. In PostgreSQL the query language and the procedural language are separate: next to plain SQL you write functions and procedures in PL/pgSQL (Procedural Language/PostgreSQL). MySQL has its own extensions too.

To see the difference concretely, let's run "the three most expensive books" on three products. The standard spelling, since SQL:2008, is FETCH FIRST:

SELECT title, list_price
FROM books
ORDER BY list_price DESC, book_id
FETCH FIRST 3 ROWS ONLY;

PostgreSQL 18.6 accepts it as is:

                       title                       | list_price
---------------------------------------------------+------------
 Design Patterns                                   |    1450.00
 The Art of Computer Programming                   |    1450.00
 Structure and Interpretation of Computer Programs |    1250.00
(3 rows)

SQL Server 2025 rejects the same statement:

Msg 153, Level 15, State 2, Server b8be943222fa, Line 4
Invalid usage of the option FIRST in the FETCH statement.

T-SQL accepts FETCH only together with OFFSET. Two T-SQL spellings that work:

/* close to the standard: OFFSET is required */
SELECT title, list_price
FROM dbo.books
ORDER BY list_price DESC, book_id
OFFSET 0 ROWS FETCH FIRST 3 ROWS ONLY;

/* T-SQL specific shortcut */
SELECT TOP (3) title, list_price
FROM dbo.books
ORDER BY list_price DESC, book_id;

Both return the same three rows. MySQL 8.4 does not know FETCH FIRST at all (ERROR 1064 (42000), a syntax error) and wants LIMIT 3; PostgreSQL supports LIMIT as well, but mistakes TOP for a function call and fails with function top(integer) does not exist. One "first three rows" need, three products, three behaviours. That is why the rule of this series is: for every topic we first show what the standard says, then the T-SQL equivalent and, when it matters, the PostgreSQL and MySQL difference. There is also a separate post that puts the dialects side by side (post 16).

In Production

Besides the dialect there is also a version dimension. In SQL Server, a database's behaviour is set by its compatibility level as much as by the server version:

SELECT name, compatibility_level, collation_name
FROM sys.databases
WHERE name = DB_NAME();

name     compatibility_level  collation_name
-------  -------------------  ----------------------------
sqlt_01  170                  SQL_Latin1_General_CP1_CI_AS

(1 rows affected)

170 is SQL Server 2025's level. A database restored from an older version can stay at the old level on the new server, and some new functions do not work at that level. Part of the gap between "works on my machine" and "works in production" comes from here. The collation sits on the same row; we will see the trouble it causes with the Turkish letter "i" in post 3.

Second point: even if you use an ORM (Object-Relational Mapping), what reaches the database is SQL. We saw how LINQ (Language Integrated Query) is translated to SQL in the Entity Framework Core (EF Core) series post "EF Core ile LINQ Sorguları: SQL Çevirisi, Client Evaluation ve IQueryable Tuzağı"; reading that translation and asking "why does this query pull 40,000 rows" takes SQL knowledge. In the Dapper series you write the SQL by hand anyway. Third point: when sending SQL from an application, never put values in by concatenating strings; this is covered in the OWASP (Open Worldwide Application Security Project) series post "OWASP A05: Injection (1) — SQL Injection ve Command Injection" and in the Dapper series post "Dapper ile Parameter'lar ve SQL Injection: Güvenli Sorgunun Kuralları" (parameters and SQL injection).

Common Pitfalls

• Treating "SQL" and "SQL Server" as the same thing. SQL is a language and an ISO standard; SQL Server, PostgreSQL, MySQL and SQLite are products that speak it. "I know SQL" does not mean the T-SQL specific TOP will run on PostgreSQL.
• Believing an ORM makes SQL knowledge unnecessary. An ORM automates writing SQL; it does not take away your responsibility to judge the SQL it produces.
• Expecting an order without ORDER BY. A table has no order; a result that arrives in primary key order today may arrive in another order tomorrow when an index is added or the plan changes.
• Treating NULL as a value. The opening query returned 5 instead of 76; every comparison with NULL yields "unknown".
• Taking the "standard" label as a portability guarantee. Most standard features are optional and some behaviour is deliberately left to the product; test on the target product before you port.

A Small Experiment

★☆☆ Run the INFORMATION_SCHEMA.COLUMNS query for the orders table. Which columns accept NULL? Explain in one sentence, as a business rule, why shipped_at is nullable.

★★☆ Change the opening query so that books without a subtitle also count as "not having this subtitle"; get 76 as the result. Hint: IS NULL with OR.

★★★ Rewrite the loop example per year instead of per category (published_year) and use SET STATISTICS IO to compare the total logical reads with the set-based GROUP BY version. How many distinct years are there, and why is the gap bigger than in the category example?

To run every query in this post on your own machine you need a lab. In the next post we will start SQL Server 2025 in Docker, connect with the mssql extension for Visual Studio Code and load the Bookstore database we will use throughout the series.