Blog
Mert Özen's blog posts on software development, backend, frontend, and DevOps.
What Is SQL? The Relational Model, the ANSI/ISO SQL Standard and the T-SQL Dialect
SQL is not a product but a declarative language based on Codd's relational model and defined by ISO/IEC 9075; SQL Server speaks its T-SQL dialect. We cover tables, rows, columns and keys, set-based thinking instead of loops with logical read counts, the DDL/DML/DCL/TCL families, and how FETCH FIRST behaves differently on SQL Server, PostgreSQL and MySQL, all with real output.
SQL Lab: Running SQL Server 2025 in Docker, the VS Code MSSQL Extension and the Bookstore Sample Database
We run SQL Server 2025 in Docker with the mcr.microsoft.com/mssql/server:2025-latest image, keeping the password in an environment variable and the data on a volume; connect with sqlcmd, the VS Code mssql extension and SSMS 22; and load the 10-table Bookstore database used throughout the series. Edition choice, Rosetta emulation on Apple Silicon and why TrustServerCertificate is for labs only are covered too.
SQL Data Types and CREATE TABLE: Numbers, Text, Date/Time and the Turkish I Collation Trap
Choose DECIMAL for money, NVARCHAR (or VARCHAR with a UTF-8 collation) for human-language text and DATETIME2 in UTC for time; on Turkish data a Latin1 collation turns UPPER('i') into 'I' and a search for 'ince' misses İnce Memed. We show FLOAT sum errors, İ and ğ silently lost in VARCHAR, DATETIME rounding 23:59:59.999 into the next day, the collation conflict error and CREATE TABLE in your own schema, with real output.
Constraints and Schema Changes: PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, DEFAULT and ALTER TABLE
Constraints are rules enforced no matter which path writes the data; in SQL Server a UNIQUE column accepts a single NULL, SQL:2023's NULLS NOT DISTINCT option is missing, and the fix is a filtered unique index. We show named constraints, IDENTITY versus SEQUENCE, ON DELETE CASCADE versus NO ACTION, CHECK letting NULL through, safe schema changes with ALTER TABLE, and how WITH NOCHECK leaves a constraint untrusted and costs the query plan.
SELECT, WHERE and ORDER BY: Logical Query Processing Order, TOP and OFFSET/FETCH
SQL evaluates a query as FROM, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, TOP, which is why a SELECT alias is invisible in WHERE but visible in ORDER BY. We show the cost of SELECT * and DISTINCT, why order is not guaranteed without ORDER BY, TOP WITH TIES, how even adding an index changes a page when OFFSET/FETCH pagination lacks a tie-breaker, and keyset pagination.
NULL and Predicates: Three-Valued Logic, LIKE, IN, BETWEEN and COALESCE
In SQL a comparison has three outcomes: TRUE, FALSE and UNKNOWN; that is why = NULL finds no rows, NOT on a nullable column also drops the NULL rows, and a single NULL in a NOT IN list empties the result. We show the type difference between COALESCE and ISNULL, guarding division by zero with NULLIF, escaping in LIKE and how a leading % affects the index, and how BETWEEN misses the last day of a date range, with real output.
Scalar Functions and CASE: Strings, Date/Time, Type Conversion and Sargability
Scalar functions are handy, but wrapped around a column in WHERE they make a query non-sargable: on 200,000 rows a YEAR()/MONTH() filter took 402 logical reads, the same month asked with a half-open range took 21. We cover CONCAT/CONCAT_WS, LEN versus DATALENGTH, CAST/CONVERT/TRY_CAST, how an nvarchar parameter loses the index through CONVERT_IMPLICIT, DATEDIFF counting boundaries, and the type and ELSE traps of CASE.
SQL JOINs 1: INNER JOIN, LEFT/RIGHT/FULL OUTER JOIN and the ON vs WHERE Difference
Joins match rows; they are not Venn diagrams: INNER JOIN keeps matched pairs, and LEFT/RIGHT/FULL OUTER JOIN also keep the preserved side's unmatched rows with NULLs. Filtering the right table of a LEFT JOIN in WHERE silently turns it into an INNER JOIN, which we see as Inner Join in the plan. We look at books without reviews, customers without orders, stock reconciliation with FULL OUTER JOIN and how one-to-many joins multiply rows, with real output.
SQL JOINs 2: Self Join, Non-Equi Join, Semi/Anti Join and CROSS APPLY vs LATERAL
Joining an order's items and payments together multiplies the totals (fan-out); the fix is to group first and join afterwards. We show CROSS JOIN report skeletons, a self join on the category tree, non-equi range joins, semi joins with EXISTS, anti joins with NOT EXISTS (same plan as LEFT JOIN … IS NULL), and every customer's last three orders with T-SQL CROSS/OUTER APPLY versus standard LATERAL in PostgreSQL and MySQL, with real output.
GROUP BY and Aggregate Functions: HAVING, Conditional Aggregation, GROUPING SETS and ROLLUP
GROUP BY collapses rows into groups, aggregates ignore NULL, and in SQL Server AVG over an integer column returns an integer. We show COUNT(*) versus COUNT(column), where WHERE and HAVING belong, STRING_AGG, conditional aggregation with CASE, a PIVOT comparison, and ROLLUP, CUBE, GROUPING SETS with GROUPING() on Bookstore data with real output.
Subqueries and Set Operators: Correlated Subqueries, EXISTS, the NOT IN Trap and UNION/INTERSECT/EXCEPT
A subquery uses one query's result inside another; NOT IN returns no rows at all if the list holds a single NULL. We cover scalar subqueries and error 512, IN/ANY/ALL, correlated subqueries, derived tables, EXISTS versus NOT EXISTS, and UNION, UNION ALL, INTERSECT and EXCEPT on Bookstore data with real output, logical reads and plan comparisons.
CTEs, Views and Recursive Queries: Readable SQL with WITH and Hierarchical Data
A CTE lets you write a query top to bottom in named steps, but SQL Server does not materialize it; every reference runs again. We show chained CTEs, views, the SELECT * trap, SCHEMABINDING and WITH CHECK OPTION, a recursive CTE walking the category tree, cycle protection and MAXRECURSION on Bookstore data with real output and logical reads.