Question 1

What are the different types of SQL joins?

Accepted Answer

**Target Audience:** Beginners who know SELECT and WHERE on one table and now need data from two tables.
**Difficulty:** Easy
**Engines:** SQLite 3.45.0 (sql.js 1.10.2, which runs the examples on this page) and PostgreSQL 16.15; MySQL and SQL Server notes come from their documentation and are marked as such.

> **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. All output was p

Question 2

What does DISTINCT do and what is its cost?

Accepted Answer

**Target Audience:** Beginners who use `SELECT DISTINCT` and want to know what it really does and why it can make a query slow.
**Difficulty:** Easy
**Engines:** SQLite 3.45.0 (sql.js 1.10.2, which runs the examples on this page) and PostgreSQL 16.15; plans were captured on both. MySQL notes are marked as not run.

> **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. All output

Question 3

How do you find duplicate rows in a table?

Accepted Answer

**Target Audience:** Beginners who know `SELECT` and want the standard duplicate-finding pattern, and candidates who want the details interviewers probe (NULLs, case, collation).
**Difficulty:** Easy
**Engines:** SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. MySQL behaviour is cited from its manual.

> **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so

Question 4

Why might EXISTS outperform a JOIN for existence checks?

Accepted Answer

**Target Audience:** Developers who filter with joins out of habit and want to understand duplicates, semi joins and the plans behind "EXISTS is faster"; beginners who know what a JOIN is.
**Difficulty:** Medium
**Engines:** SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page; MySQL notes cite its manual.

> **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so

Question 5

What is the difference between EXISTS and IN?

Accepted Answer

**Target Audience:** Beginners who know a basic WHERE clause and have seen both forms in code reviews; anyone who has heard "EXISTS is faster" and wants to know when that is true.
**Difficulty:** Medium
**Engines:** SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. MySQL behaviour is cited from its manual.

> **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:`

Question 6

How do you update a table based on another table?

Accepted Answer

**Target Audience:** Developers who can write a simple UPDATE and now need to drive it from another table, such as a staging import; beginners who want the engine differences laid out.
**Difficulty:** Medium
**Engines:** SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. MySQL and SQL Server syntax is cited from their manuals and was not run here.

> **How to read this doc:** Every idea is explained in plain language fir

Question 7

What is an UPSERT?

Accepted Answer

**Target Audience:** Beginners who know INSERT and UPDATE and want to learn UPSERT from zero; candidates who need the one-minute answer and the engine differences.
**Difficulty:** Medium
**Engines:** SQLite 3.45.0 (sql.js 1.10.2, which runs the examples on this page) and PostgreSQL 16.15 (the same examples plus MERGE, and two real `psql` sessions for the race). MySQL and SQL Server facts are cited, not run.

> **How to read this doc:** Every idea is explained in plain language first, then tagged

Question 8

What is the difference between NOT IN and NOT EXISTS when NULLs are involved?

Accepted Answer

**Target Audience:** Anyone who has written "find the rows that have no match" and been surprised by an empty result; beginners meeting NULL logic for the first time.
**Difficulty:** Medium
**Engines:** SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page; MySQL notes cite its manual.

> **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exa

Question 9

UPSERT with INSERT … ON CONFLICT — how do you insert or update atomically without races?

Accepted Answer

**Target Audience:** Backend developers who write "create or update" code and candidates who will be asked why the naive version is unsafe.
**Difficulty:** Medium
**Engines:** PostgreSQL 16.15 (concurrency tests, the batch example) and SQLite 3.45.0 through sql.js 1.10.2 (the runnable examples on this page). MySQL and SQL Server syntax notes are cited from their documentation, not run.

> **How to read this doc:** Every idea is explained in plain language first. Then a `📌 Interview term:` callo

SQLDECODED.

Easy