Blog
Mert Özen's blog posts on software development, backend, frontend, and DevOps.
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.
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.
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.
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.
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.
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.
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.