Free lesson · Mastering SQL

A Note on SQL Dialects - Why Your Query Works Here but Breaks There

Easy10 min readsqldatabase

Alex: Okay, one thing I keep seeing online that confuses me. Someone posts a SQL query on Stack Overflow, and the first three comments say "this only works in PostgreSQL" or "that is MySQL syntax" or "use TOP instead of LIMIT for SQL Server." What is going on? I thought SQL was one language.

Emeka: You just hit one of the most frustrating things about SQL for learners, and honestly, for professionals too. So let me explain it properly, because if you do not understand this now, it will bite you repeatedly throughout your learning journey and definitely in interviews.

SQL is a standard. There is an official specification called the SQL standard, maintained by ISO and ANSI. The most recent major versions are SQL:2016 and SQL:2023. The standard defines what SQL should look like - the syntax, the semantics, the behavior.

But here is the problem: no database engine implements the standard completely, and every major engine adds its own extensions on top of it. PostgreSQL adds things the standard does not include. MySQL adds different things. SQL Server adds its own set. Oracle has extensions that have existed for decades and are so widely used that some people think they are standard SQL.

The result is that SQL is not one language. It is a family of closely related dialects that share a common core but diverge on the edges. And those edges are exactly where interview questions like to live.

Alex: So which dialect should I learn?

Emeka: Good question, and I have a clear answer. You should learn standard SQL first, because the core - SELECT, FROM, WHERE, JOIN, GROUP BY, HAVING, ORDER BY, window functions, CTEs - is the same everywhere. Ninety percent of what you write in this course will run identically on PostgreSQL, MySQL, SQL Server, and SQLite.

For the remaining ten percent - the parts that diverge - this course uses PostgreSQL as the primary dialect. There are three reasons for that choice.

Why PostgreSQL Is the Primary Dialect

graph TD
    A["SQL Standard<br/>(ISO/ANSI)"] --> B["PostgreSQL"]
    A --> C["MySQL"]
    A --> D["SQL Server"]
    A --> E["SQLite"]
    A --> F["Oracle"]
    
    B --> G["Closest to the standard"]
    B --> H["Most complete feature set"]
    B --> I["Most common in FAANG interviews"]
    
    style A fill:#6366f1,color:#fff
    style B fill:#f59e0b,color:#000
    style C fill:#94a3b8,color:#000
    style D fill:#94a3b8,color:#000
    style E fill:#94a3b8,color:#000
    style F fill:#94a3b8,color:#000

This diagram shows the SQL standard at the top, with arrows pointing down to five database engines: PostgreSQL, MySQL, SQL Server, SQLite, and Oracle. PostgreSQL is highlighted in gold, with three notes beneath it: closest to the standard, most complete feature set, and most common in FAANG interviews. The other four engines are shown in gray.

First, PostgreSQL is the closest major database to the SQL standard. When the standard says window functions should behave a certain way, PostgreSQL generally follows that specification. When the standard defines CTEs, PostgreSQL's implementation matches. This means that if you learn SQL through PostgreSQL, you are learning the most standard version of the language, and your knowledge transfers more cleanly to other engines.

Second, PostgreSQL has the most complete feature set. It supports window functions with full frame clause control (ROWS, RANGE, and GROUPS). It supports LATERAL joins. It supports recursive CTEs with cycle detection. It supports FILTER clauses on aggregates. It supports DISTINCT ON. It supports GENERATE_SERIES for creating data on the fly. Many of these features either do not exist or work differently in MySQL or SQLite. If I teach you PostgreSQL, you have access to every tool. If I taught you MySQL first, you would hit walls.

Third, PostgreSQL is the most common dialect in FAANG interview environments. When you take a SQL interview at Meta, Google, Amazon, or Netflix, the interview platform typically runs PostgreSQL or a PostgreSQL-compatible engine. This is not universal - some companies use proprietary environments - but PostgreSQL is the safest bet for interview preparation. If your query runs on PostgreSQL, it will almost certainly run in the interview sandbox.

Alex: So I should just ignore the other databases?

Emeka: No. You should know where they differ, because sometimes your interview will be on MySQL, or your job will use SQL Server, or you will be querying a SQLite database embedded in a mobile app. The differences matter. But you need a home base to learn from, and PostgreSQL is that home base.

The Four Engines You Will Encounter

Let me give you a quick profile of each major dialect so you know what to expect.

PostgreSQL

The standard-bearer. Open source, incredibly feature-rich, strong community. Used heavily in startups, web companies, and increasingly in large enterprises. At Netflix, our data lake is built on open-source tools that speak PostgreSQL-compatible SQL through Trino and Presto.

PostgreSQL's strengths: standards compliance, advanced indexing (GIN, GiST, BRIN), full window function support, JSONB for document storage, excellent extension ecosystem (PostGIS for geospatial, pg_trgm for fuzzy matching, TimescaleDB for time series).

PostgreSQL's personality: it does things the "right" way according to the standard, even when that means being stricter than other engines. If you write sloppy SQL that MySQL would quietly accept, PostgreSQL will reject it with a clear error message. That strictness is a feature - it teaches you correct SQL from the start.

MySQL

The most deployed database in the world by install count. Powers WordPress, most PHP applications, and a huge portion of the web. Facebook (Meta) historically ran one of the largest MySQL deployments on Earth.

MySQL's strengths: fast for simple read-heavy workloads, excellent replication support, massive community, very forgiving of imprecise SQL (which is both a strength and a weakness).

MySQL's personality: permissive. It will accept queries that are technically wrong according to the standard and give you an answer anyway - which can be dangerous because you think your query is correct when it is silently doing something unexpected. The classic example: MySQL's traditional behavior with GROUP BY allows you to select non-aggregated columns that are not in the GROUP BY clause, which should be an error but MySQL returns an arbitrary value from the group instead. Newer MySQL versions with ONLY_FULL_GROUP_BY mode fix this, but you will encounter legacy behavior.

SQL Server (Microsoft)

The enterprise database. Dominant in large corporations, financial services, healthcare, and government. If you work at a Fortune 500 company, there is a very high chance SQL Server is somewhere in the stack.

SQL Server's strengths: excellent management tools (SSMS), strong integration with the Microsoft ecosystem, good performance at scale, mature stored procedure support.

SQL Server's personality: it has its own vocabulary for things. What PostgreSQL calls LIMIT, SQL Server calls TOP. What PostgreSQL calls ILIKE, SQL Server handles through COLLATE. What standard SQL calls a Common Table Expression, SQL Server also calls a CTE - but it adds CROSS APPLY and OUTER APPLY as alternatives to LATERAL JOIN. Same concepts, different syntax.

SQLite

The embedded database. It is not a server - it is a library that runs inside your application. Every iPhone, every Android phone, every web browser, every copy of Python has SQLite built in. It is the most deployed database engine in the world by a factor of thousands.

SQLite's strengths: zero configuration, no server to manage, the entire database is a single file, perfect for local development and testing.

SQLite's personality: minimalist. It supports a subset of SQL. No stored procedures. No RIGHT JOIN (until recently). Limited ALTER TABLE support. Flexible typing system where any column can hold any type (which surprises people coming from PostgreSQL). Great for learning the basics, but you will outgrow it quickly if you need advanced features.

A Word on Oracle

Oracle Database is the veteran - it has been around since 1979 and dominates in banking, telecommunications, government, and large enterprise. You are less likely to encounter it in a startup or a FAANG interview, but if you work in finance or consulting, you will almost certainly touch Oracle.

Oracle's personality: powerful but proprietary. It pioneered many features that later became standard (analytic functions, flashback queries, materialized views), and it has its own syntax for things other databases do differently. NVL() instead of COALESCE(). ROWNUM instead of LIMIT. CONNECT BY instead of recursive CTEs (though modern Oracle supports CTEs too). Oracle's SQL dialect is rich but distinct enough that moving between Oracle and PostgreSQL requires real adjustment.

This course does not cover Oracle-specific syntax in detail, but when an Oracle difference is particularly important - like ROWNUM behavior or the (+) join syntax you might see in legacy code - I will note it.

Where the Dialects Diverge - The Big Differences

Here are the areas where the divergence is large enough that it will affect your work. This is not an exhaustive list - it is the differences that matter most for learning and interviews.

String Concatenation

Limiting Results

Auto-Incrementing Primary Keys

Upsert (Insert or Update)

Date and Time

Alex: That is a lot of differences for what is supposed to be one language.

Emeka: It is. And this is exactly why I want you to see them now, at the beginning of the course, instead of being surprised by them later. The core SQL - SELECT, FROM, WHERE, JOIN, GROUP BY, HAVING, ORDER BY, window functions, CTEs - works the same everywhere. The divergence is in utility functions, date handling, auto-increment syntax, and edge-case behaviors.

How This Course Handles Dialect Differences

Here is the convention I use throughout this course. Once you know it, you will never be surprised.

Default: PostgreSQL syntax. Every code example in this course uses PostgreSQL syntax unless otherwise stated. If you paste it into a PostgreSQL database, it will run.

Dialect callouts when it matters. Whenever a concept has significant dialect differences, you will see a callout box like this:

Warning: Dialect difference: PostgreSQL uses LIMIT for row limiting. SQL Server uses TOP in the SELECT clause instead (SELECT TOP 10 * FROM users). SQL Server 2012+ also supports the standard OFFSET ... FETCH syntax. MySQL and SQLite use LIMIT like PostgreSQL.

These callout boxes appear only when the difference is significant enough to affect your work or your interview. I am not going to flag every minor naming variation - only the ones that change behavior or break your query.

Comparison tables for function differences. When an entire category of functions differs across dialects - like date functions or string functions - I provide a comparison table so you can see all four dialects side by side.

Interview tip callouts for dialect-sensitive questions. If a SQL interview question is known to be dialect-sensitive, I will flag it:

Interview: If the interviewer does not specify a dialect, ask. Say: "I will write this in PostgreSQL syntax - is that what your environment supports?" This is not a sign of weakness. It is a sign that you understand SQL is a family of dialects and you want to write code that actually runs. Most interviewers will either confirm PostgreSQL or tell you which dialect to use. Either way, you have demonstrated awareness.

Alex: That makes sense. So I learn PostgreSQL, and you tell me when the other engines do something different.

Emeka: Exactly. And here is the practical benefit: once you are comfortable with PostgreSQL, switching to MySQL or SQL Server is a matter of learning a relatively small set of syntax differences. The mental models - how joins work, how window functions operate, how query planners make decisions - those are the same everywhere. The models are what take months to learn. The syntax differences take a weekend.

Which Dialect Will Your Interview Use?

Alex: For interviews specifically, should I worry about which database the interview platform uses?

Emeka: In most cases, no. Here is what I have seen across the major companies:

Meta typically uses a PostgreSQL-compatible environment for their SQL interviews. Standard SQL and PostgreSQL syntax both work.

Amazon varies - some teams use their own internal tools, some use standard SQL environments. Amazon interviews tend to focus more on the logic of the query than on dialect-specific syntax. If you write clean standard SQL, you will be fine.

Google uses internal interview platforms that accept standard SQL. Google's BigQuery uses its own dialect (GoogleSQL), but the interview environment is usually more standard.

Netflix uses PostgreSQL-compatible tools extensively. Given that our data stack is built on open-source technologies that speak PostgreSQL-compatible SQL, interview questions are typically written with PostgreSQL in mind.

The general rule: if you can write it in PostgreSQL, you can write it in any FAANG interview. There are rare exceptions, but preparing in PostgreSQL is the highest-probability strategy.

Tip: Practical advice for right now: If you do not have a database set up yet, go to DB Fiddle (db-fiddle.com) and select PostgreSQL from the dropdown. You can paste any code example from this course and run it. No installation, no setup, no configuration. When you are ready for a local environment later, we will point you in the right direction.

Alex: Okay, I think I am actually ready now. Foundations, then querying, then the whole journey.

Emeka: Then let us begin. Module 1, Topic 1: What is SQL. We start from the very beginning.

Tip: Your next step: Move on to Module 1, Topic 1 - What is SQL? - where we start from absolute zero and build your first mental model of how SQL works.

Back to Mastering SQL