SQL Fundamentals Interview Questions

“There are multiple ways to find the second highest salary, but the most reliable and interview-friendly approach is using DENSE_RANK.”

• Best approach: DENSE_RANK()

  • Assigns ranks based on salary in descending order
  • Highest salary gets rank 1, second highest gets rank 2
  • Correctly handles duplicate salaries

  SELECT salary
  FROM (
      SELECT salary,
             DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
      FROM Employee
  ) t
  WHERE rnk = 2;
  

• Alternative approach — MAX with subquery

  • First find the highest salary
  • Then get the maximum salary less than that

  SELECT MAX(salary)
  FROM Employee
  WHERE salary < (SELECT MAX(salary) FROM Employee);
  

• Other option — ORDER BY with OFFSET

  • Works, but not always preferred in interviews

“Prefer DENSE_RANK in real-time, because it’s more robust and handles edge cases like duplicate salaries properly.”

“The goal here is to remove duplicate records while keeping one unique row, and the most preferred approach is using ROW_NUMBER.”

• Best approach — ROW_NUMBER() with CTE

  • Identify duplicates using PARTITION BY
  • Assign row numbers to each duplicate group
  • Keep the first row and delete the rest

  WITH cte AS (
      SELECT *,
             ROW_NUMBER() OVER (PARTITION BY name, salary ORDER BY id) AS rn
      FROM Employee
  )
  DELETE FROM cte
  WHERE rn > 1;
  

• Alternative approach — DISTINCT with temp table

  • Copy unique records into a new table
  • Truncate original table and reinsert data

• GROUP BY (supporting approach)

  • Used to identify duplicates, not directly delete them

Both filter rows, but at different stages of SQL execution.

WHERE: Filters individual rows BEFORE grouping. Applied to raw rows from the table. Cannot use aggregate functions (SUM, COUNT, AVG) in WHERE because aggregations haven't happened yet.

HAVING: Filters groups AFTER GROUP BY. Applied to the aggregated result. Can use aggregate functions because aggregation has already happened.

SQL execution order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

Analogy:

• WHERE = security at the door (decides who enters before anything starts)
• HAVING = bouncer who removes tables after seeing the total bill (checks the group's result)

Performance tip: Use WHERE whenever possible — it reduces rows before aggregation (less work for GROUP BY). HAVING filters after grouping — the database already did the aggregation work for filtered-out groups.

-- WHERE: filter rows BEFORE grouping
SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE hire_date >= '2022-01-01'
GROUP BY department;

-- HAVING: filter groups AFTER grouping
SELECT department, COUNT(*) AS emp_count, SUM(salary) AS total_salary
FROM employees
GROUP BY department
HAVING COUNT(*) > 5 AND SUM(salary) > 500000;

-- COMBINATION: both WHERE and HAVING
SELECT
    region,
    category,
    COUNT(*) AS transaction_count,
    SUM(amount) AS total_sales
FROM orders
WHERE order_date >= '2024-01-01' AND status = 'COMPLETED'
GROUP BY region, category
HAVING SUM(amount) > 100000 AND COUNT(*) >= 50
ORDER BY total_sales DESC;

-- ERROR: Cannot use aggregate in WHERE
SELECT department FROM employees
WHERE AVG(salary) > 80000; 
-- Fix: use HAVING instead

SQL joins combine rows from two or more tables based on a related column. The join type controls which rows appear in the result:

INNER JOIN:

Only rows where the join condition matches in BOTH tables. Most selective — returns the fewest rows.

-- orders that have a matching customer
SELECT o.order_id, o.amount, c.customer_name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;

LEFT JOIN:

ALL rows from the left table + matching rows from right. Where no match exists, right columns are NULL. 'Show me everything on the left, add right data where available.'

-- all orders, customer info where exists
SELECT o.order_id, o.amount, c.customer_name  -- NULL if no match
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id;

RIGHT JOIN:

ALL rows from the right table + matching from left. Less common — usually rewritten as a LEFT JOIN with tables swapped.

-- (same as LEFT JOIN with tables swapped)
SELECT o.order_id, c.customer_name
FROM orders o
RIGHT JOIN customers c ON o.customer_id = c.customer_id;

FULL JOIN:

ALL rows from BOTH tables. NULL where no match on either side.

SELECT o.order_id, c.customer_name
FROM orders o
FULL OUTER JOIN customers c ON o.customer_id = c.customer_id;

SELF JOIN:

A table joined to itself. Used for hierarchical data, finding pairs, comparing rows within the same table.

-- find employees and their managers
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

CROSS JOIN:

Every row from left × every row from right (cartesian product). N left rows × M right rows = N×M rows. Used for generating combinations.

CROSS JOIN: every order combined with every customer
SELECT o.order_id, c.customer_name
FROM orders o
CROSS JOIN customers c;

• Returns the Cartesian product:
• each row from orders paired with every row from customers
• Example:
• If orders has 3 rows and customers has 4 rows,
• result will contain 3 × 4 = 12 rows

Both combine result sets from multiple SELECT statements vertically (same columns, stacked rows), but they differ in how they handle duplicates.

UNION: Removes duplicate rows from the combined result — performs a DISTINCT operation. This requires sorting or hashing the entire result to find duplicates — expensive.

-- removes duplicates (expensive — sorts/hashes combined result)
SELECT customer_id FROM orders_2023
UNION
SELECT customer_id FROM orders_2024;

• If customer-001 appears in both years: only ONE row for customer-001

UNION ALL: Keeps ALL rows including duplicates. No deduplication step — just concatenates. Much faster.

-- keeps all rows (fast — just concatenate)
SELECT customer_id, order_date, amount FROM orders_2023
UNION ALL
SELECT customer_id, order_date, amount FROM orders_2024;

• All rows from both years, including any duplicates
• Customer-001 in both years → appears twice

Tips: Always use UNION ALL unless you specifically need to remove duplicates. UNION ALL is always faster because it skips the expensive deduplication step. If you know the queries return different data (e.g., different date ranges, different status values), UNION ALL is always correct AND faster.

-- (data from different partitions can't have logical duplicates)
SELECT * FROM orders WHERE region = 'North'
UNION ALL  -- use ALL — North and South are mutually exclusive
SELECT * FROM orders WHERE region = 'South';

When UNION ALL is correct AND faster:

• Different partitions of the same data: Orders from region='North' UNION ALL Orders from region='South'. North and South are mutually exclusive — no duplicates possible. Use UNION ALL.
• Different time windows: Sales from 2022, 2023, 2024. Each year's data is distinct. No duplicate rows across years. Use UNION ALL.
• Combining different entities: Inserting from staging tables to final — each table has different data. Use UNION ALL.
• ETL combination of multiple sources: Different source systems feed separate tables — same record shouldn't appear in both. Use UNION ALL.

When you genuinely need UNION:

• Combining results where the same entity might appear in multiple queries and you only want it once
• Finding unique set of values across multiple tables (customer IDs from different tables, want each once)

All three let you create a named intermediate result for use in a query, but they differ in scope, performance, and use cases.

CTE (Common Table Expression):

• Defined with WITH clause at top of query.
• Exists ONLY for that one query execution.
• Not stored anywhere — optimizer may inline it.
• Can be recursive (WITH RECURSIVE for hierarchies).
• Best for: readable multi-step queries, recursive traversals, referencing same result multiple times in one query.

WITH total_sales AS (
    SELECT customer_id, SUM(amount) total
    FROM orders
    GROUP BY customer_id
)
SELECT * FROM total_sales
WHERE total > 500;

Subquery:

• Nested inline within the main query.
• Same scope as CTE — exists only for that query.
• Cannot be referenced multiple times (must be repeated).
• Can be correlated (reference outer query columns).
• Best for: simple single-use intermediate results, correlated logic.

SELECT * FROM (
    SELECT customer_id, SUM(amount) total
    FROM orders
    GROUP BY customer_id
) x
WHERE total > 500;

Temp Table (#table in SQL Server, @table for table variable):

• Physically stored in tempdb (or memory).
• Persists for the duration of the session or procedure.
• Can have indexes, statistics, constraints.
• Can be referenced multiple times in multiple subsequent queries.
• Useful for large intermediate results that benefit from indexing.
• Best for: large intermediate datasets, results used by multiple subsequent queries, when you need indexes on the intermediate result.

CREATE TABLE #sales (
    customer_id INT,
    total DECIMAL(10,2)
);

INSERT INTO #sales
SELECT customer_id, SUM(amount)
FROM orders
GROUP BY customer_id;

SELECT * FROM #sales
WHERE total > 500;

An index is a database data structure (B-tree by default) that enables fast data lookup without scanning every row. Like a book index, lets you jump directly to the relevant pages.

Without index: Full table scan on every query, reads every row from disk. On 100M rows: potentially minutes. With index: Direct seek to matching rows, in milliseconds.

Clustered Index:

• Determines the PHYSICAL SORT ORDER of data rows on disk.

• Data pages ARE the index — leaf level of the B-tree contains the actual data.

• ONE per table (data can only be physically sorted one way).

• Created automatically on PRIMARY KEY in SQL Server.

• Excellent for range queries: WHERE date BETWEEN '2024-01-01' AND '2024-01-31' (physically adjacent rows).

  CREATE CLUSTERED INDEX idx_order_date ON orders(order_date);
  

  • Data physically sorted by order_date.

  SELECT * FROM orders
  WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31';
  

  • Fast range query, rows are physically near each other.

Non-Clustered Index:

• A SEPARATE structure from the table data.

• Stores: index key + pointer (row locator) back to the data page.

• Multiple allowed per table (up to 999 in SQL Server).

• For columns frequently used in WHERE, JOIN, ORDER BY that aren't the PK.

• Key Lookup: after finding rows via index, must follow the pointer to get non-indexed columns.

  CREATE NONCLUSTERED INDEX idx_customer ON orders(customer_id);
  
  SELECT * FROM orders
  WHERE customer_id = 101;
  

  • Index finds matching rows, then jumps back to actual table data.

Covering Index (Non-clustered optimization): Include SELECT columns in the index itself — no Key Lookup needed:

CREATE INDEX idx_customer ON orders (customer_id) INCLUDE (amount, status);

• Query for customer_id now finds amount and status directly in index.

Normalization is the process of organizing a relational database to reduce data redundancy and improve data integrity. Each Normal Form (NF) adds additional constraints to eliminate specific types of anomalies.

1NF (First Normal Form):

Each column contains atomic (indivisible) values. No repeating groups or arrays in a single cell. Each row is unique.

Real-World Scenario: An e-commerce platform stores customer orders.

Before 1NF (Violation):

The products_ordered column holds multiple values in one cell. If you want to find all orders containing a "Mouse", you'd have to scan and split strings, messy and error-prone.

After 1NF (Fix): Create a separate order_items table, one row per product per order.

2NF (Second Normal Form):

Must be in 1NF AND every non-key attribute must depend on the ENTIRE primary key (no partial dependencies). Only applies to composite (multi-column) primary keys.

Real-World Scenario: The same e-commerce platform tracks what was ordered and product details.

Before 2NF (Violation): order_items(order_id, product_id, quantity, product_name, product_price)

Primary key → (order_id, product_id)

product_name and product_price depend only on product_id, not on the full key (order_id, product_id). This is a partial dependency. Notice "Laptop, ₹75,000" is stored twice — if the price changes, you'd have to update multiple rows.

After 2NF (Fix): Split into two tables. order_items — only what's truly tied to both order + product:

products — product details depend only on product_id:

Now updating a product's price means changing one row in one table.

3NF (Third Normal Form):

Must be in 2NF AND every non-key attribute depends ONLY on the primary key — no transitive dependencies (non-key column depends on another non-key column).

Real-World Scenario: A company HR system stores employee information. Before 3NF (Violation): employees(emp_id, emp_name, dept_id, dept_name, dept_location)

dept_name and dept_location depend on dept_id, not directly on emp_id. This is a transitive dependency: emp_id → dept_id → dept_name. "Engineering, Bangalore" is repeated for every employee in that department — a rename means updating many rows.

After 3NF (Fix): Move department info to its own table.

employees:

departments:

If the Engineering team moves to Hyderabad, you update one row — done.

Quick memory trick:

• 1NF: No repeating groups (atomic values)
• 2NF: No partial key dependencies (everything depends on the whole key)
• 3NF: No transitive dependencies (nothing depends on a non-key column)

All three remove data, but at very different levels with very different implications.

DELETE (removes rows, keeps table):

• DML (Data Manipulation Language)

• Removes specific rows based on a WHERE clause (or all rows without WHERE)

• Logged row-by-row — each deletion is written to the transaction log → slow for large tables

• CAN be rolled back within a transaction

• Triggers fire on DELETE

• Table structure, indexes, constraints all remain

  BEGIN TRANSACTION;
      DELETE FROM orders WHERE order_date < '2026-01-01';
  ROLLBACK; -- or COMMIT;
  
  -- DELETE all rows (slow for large tables):
  DELETE FROM staging_table;  -- row-by-row log, triggers fire
  

TRUNCATE (removes ALL rows, keeps table structure):

• DDL (Data Definition Language) in most databases

• Removes all rows instantly — minimal logging (just deallocates pages)

• Much faster than DELETE for clearing a whole table

• Cannot be rolled back (in SQL Server — you CAN rollback in PostgreSQL if wrapped in explicit transaction)

• Does NOT fire row-level triggers

• Resets identity/auto-increment counters

• Table structure, indexes, constraints remain

  TRUNCATE TABLE staging_table;
  

  • fast, no row log, triggers don't fire
  • Identity counter resets to seed value

DROP (removes the entire table):

• DDL

• Removes everything: rows, structure, indexes, constraints, permissions, triggers

• The table ceases to exist entirely

• Cannot be undone without a backup

  DROP TABLE IF EXISTS old_staging_table;
  

Practical use in ETL:

• Before loading a staging table:

  TRUNCATE TABLE staging.orders;  -- fast clear
  

• Then: INSERT/bulk load
• To remove only last month's data:

  DELETE FROM orders 
        WHERE YEAR(order_date) = 2023 AND MONTH(order_date) = 12;
  

Interview summary:

• Remove specific rows → DELETE
• Empty a table quickly → TRUNCATE
• Get rid of the table entirely → DROP

NULL represents an unknown or missing value in SQL. The critical thing to understand is that SQL uses THREE-VALUED LOGIC: TRUE, FALSE, and UNKNOWN. Any comparison with NULL returns UNKNOWN, not TRUE or FALSE. So WHERE column = NULL always returns zero rows — you must use IS NULL.

Key NULL rules:

• NULL = NULL → UNKNOWN (not TRUE)
• NULL <> NULL → UNKNOWN
• 5 + NULL → NULL
• COUNT(column) → ignores NULLs; COUNT(*) → counts all rows
• SUM(NULL_column) → NULL if ALL rows are NULL; otherwise sums non-NULL values

NULL traps:

• NOT IN with NULLs: if the subquery returns even one NULL, NOT IN returns no rows
• ORDER BY: NULLs sort last by default in SQL Server, first in some others
• JOINs: NULL keys never match, NULL = NULL is UNKNOWN in join conditions

= NULL evaluates to UNKNOWN (not TRUE, not FALSE). In WHERE clauses, only rows where the condition is TRUE are returned. Rows with UNKNOWN are excluded, which means WHERE column = NULL returns zero rows, always.

The correct syntax:

• WHERE column IS NULL: returns rows where column has no value
• WHERE column IS NOT NULL: returns rows where column has a value
• WHERE column = 'value' OR column IS NULL: match a specific value OR null

WRONG:

SELECT * FROM employees WHERE manager_id = NULL;      -- returns 0 rows always!
SELECT * FROM employees WHERE manager_id <> NULL;     -- returns 0 rows always!
SELECT * FROM employees WHERE NULL = NULL;            -- returns 0 rows!

CORRECT:

SELECT * FROM employees WHERE manager_id IS NULL;     -- employees with no manager
SELECT * FROM employees WHERE manager_id IS NOT NULL; -- employees WITH a manager

Both are named, reusable blocks of SQL code, but they differ in purpose, return behavior, and where they can be used.

When to use Stored Procedure:

• Multi-step business logic that modifies data
• ETL operations, data migration steps
• Complex workflows with error handling and rollback
• When you need to return multiple result sets

When to use Function:

• Calculation used in a SELECT: SELECT calculate_tax(salary), SELECT format_date(date_col)
• Reusable expression that takes input and returns a value
• Table-valued functions for reusable subqueries

-- Creating STORED PROCEDURE
CREATE PROCEDURE usp_transfer_funds
    @from_account INT,
    @to_account   INT,
    @amount       DECIMAL(10,2)
AS
BEGIN
    BEGIN TRANSACTION
    BEGIN TRY
        UPDATE accounts SET balance = balance - @amount WHERE account_id = @from_account;
        UPDATE accounts SET balance = balance + @amount WHERE account_id = @to_account;
        COMMIT TRANSACTION;
        PRINT 'Transfer successful';
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        THROW;  -- re-raise the error
    END CATCH
END;

-- Call it:
EXEC usp_transfer_funds @from_account=1, @to_account=2, @amount=500.00;

-- Creating FUNCTION
CREATE FUNCTION fn_calculate_tax (@salary DECIMAL(10,2))
RETURNS DECIMAL(10,2)
AS
BEGIN
    RETURN CASE
        WHEN @salary > 100000 THEN @salary * 0.30
        WHEN @salary > 50000  THEN @salary * 0.20
        ELSE @salary * 0.10
    END
END;

-- Use in SELECT, stored procedure can't be used here!
SELECT name, salary, dbo.fn_calculate_tax(salary) AS tax_amount
FROM employees;

VIEW: A View is a virtual table, it stores only the SQL query definition, not the actual data. Every time you query a view, it runs the underlying query fresh and fetches real-time data from the base tables.

MATERIALIZED VIEW: A Materialized View is a physical table, it stores the actual query result on disk. The data is precomputed and saved, so querying it is much faster, but the data can become stale until refreshed.

View Example:

Scenario: You want to always see the total orders per customer.

Creating the View:

CREATE VIEW customer_order_summary AS
SELECT customer_id, COUNT(order_id) AS total_orders
FROM orders
GROUP BY customer_id;

Every time you query customer_order_summary, it re-runs the SELECT on the orders table and returns fresh data.

Base Table orders:

Querying the View:

SELECT * FROM customer_order_summary;

If a new order is added to orders, the view automatically reflects it next time you query — because it runs live.

Materialized View Example

Scenario: Same summary, but the orders table has millions of rows. Running COUNT every time is slow.

Creating the Materialized View:

CREATE MATERIALIZED VIEW customer_order_summary_mv AS
SELECT customer_id, COUNT(order_id) AS total_orders
FROM orders
GROUP BY customer_id;

The result is physically stored on disk at creation time:

Now if a new order is inserted into orders, the materialized view does NOT update automatically. It still shows old data until you manually refresh:

REFRESH MATERIALIZED VIEW customer_order_summary_mv;

Key Difference Table

Both functions deal with NULL values but in opposite directions. COALESCE replaces NULLs, NULLIF creates NULLs.

COALESCE(expr1, expr2, ..., exprN): Returns the FIRST non-NULL value from the list. Classic use: provide a fallback default when a value might be NULL.

NULLIF(expr1, expr2): Returns NULL if the two expressions are EQUAL, otherwise returns expr1. Classic use: avoid divide-by-zero errors by converting zero denominators to NULL (dividing by NULL = NULL, not an error).

COALESCE: first non-NULL:

• Customer contact preference: try phone, then email, then 'Unknown'

SELECT
    customer_id,
    COALESCE(phone, mobile, email, 'No contact info') AS best_contact,
    COALESCE(bonus, 0) AS bonus_safe,                 -- default 0 if NULL
    COALESCE(first_name + ' ' + last_name, company_name, 'Unknown') AS display_name
FROM customers;

NULLIF: return NULL if equal:

• Avoid divide by zero in % calculations:

SELECT
    region,
    actual_sales,
    target_sales,
    ROUND(100.0 * actual_sales / NULLIF(target_sales, 0), 2) AS pct_of_target
FROM sales_targets;

• Without NULLIF: error when target_sales = 0

• actual_sales / target_sales → division by zero!

• With NULLIF: returns NULL instead of error

• Another use: treat empty string as NULL

SELECT NULLIF(phone_number, '') AS clean_phone  -- '' becomes NULL
FROM customers;

Combining COALESCE + NULLIF:

SELECT
    COALESCE(NULLIF(TRIM(phone), ''), 'No phone') AS phone_clean
FROM customers;

• 1st TRIM then NULLIF empty string → COALESCE to default

SQL queries are NOT executed in the order they are written. This is one of the most important concepts to understand, it explains why certain things are allowed or not allowed in certain clauses.

Logical execution order (6 steps):

1. FROM / JOIN: The database identifies all the tables involved and builds the full working dataset by executing all joins. This is the starting point — everything else operates on this result.

2. WHERE: Filters individual rows from the FROM result. Only rows where the condition is TRUE are kept. Aggregate functions (SUM, COUNT) CANNOT be used here, they haven't been computed yet. NULL comparisons: always use IS NULL, not = NULL.

3. GROUP BY: Groups the filtered rows into buckets by the specified columns. Each group produces one row in the next step.

4. HAVING: Filters the GROUPS produced by GROUP BY. Aggregate functions CAN be used here (they're now computed). This is why HAVING COUNT(*) > 5 works but WHERE COUNT(*) > 5 fails.

5. SELECT: Defines which columns and expressions appear in the output. Column aliases defined here are NOT available in WHERE or GROUP BY (they haven't been evaluated yet). DISTINCT is applied here.

6. ORDER BY: Sorts the final result. The only clause that CAN use SELECT aliases (because SELECT has already been evaluated). TOP/LIMIT is also applied here.

Why this matters:

• Can't reference SELECT alias in WHERE or GROUP BY → use the full expression
• Can't use SUM() in WHERE → move to HAVING
• NULL behavior: WHERE filters are evaluated after FROM but before aggregation

Interview trap:

• This FAILS: alias 'total' not yet defined at WHERE stage

SELECT SUM(amount) AS total FROM orders WHERE total > 1000;

• This WORKS: HAVING is evaluated after SELECT logic

SELECT region, SUM(amount) AS total FROM orders GROUP BY region HAVING SUM(amount) > 1000;

A SELF JOIN is when you join a table to ITSELF — treating the same table as if it were two separate tables using aliases. It's used for hierarchical data (employee-manager relationships), comparing rows within the same table, or finding pairs of records.

Common use cases:

• Employee and their manager (both in the same employees table)
• Customer referral chains (who referred whom — same customers table)
• Product price comparison over time (same product table, different dates)
• Finding records that meet conditions relative to other records in the same table

Find employees earning more than their manager

SELECT e.name AS employee, e.salary, m.name AS manager, m.salary AS mgr_salary
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id
WHERE e.salary > m.salary;

A CROSS JOIN produces the Cartesian product of two tables, every row from the left table combined with every row from the right table. N rows × M rows = N×M rows.

Why it's dangerous:

• Most accidental cross joins come from forgetting a JOIN condition.
• 1000 rows × 1 million rows = 1 billion rows can crash a query engine.

Real Scenario-1

• Generate all region × product combinations for a forecast template

SELECT r.region, p.product_name
FROM regions r
CROSS JOIN products p
ORDER BY r.region, p.product_name;

• Result: 5 regions × 20 products = 100 rows

  North	Widget A
  North	Widget B
  North	Widget C
  ... (all 100 combinations)

Real Scenario-2

• Generate date series × accounts

SELECT a.account_id, d.calendar_date
FROM accounts a
CROSS JOIN date_dimension d
WHERE d.calendar_date BETWEEN '2025-01-01' AND '2025-12-31'
ORDER BY a.account_id, d.calendar_date;

• Creates 365 rows per account — useful for spotting missing days

INTERSECT returns only the rows that are common in both SELECT queries, like a common area between two sets.

EXCEPT returns rows from the first SELECT that are not present in the second SELECT, like subtracting one set from another.

Both work like set operations, similar to UNION. Both queries must have the same number of columns and compatible data types.

INTERSECT Example:

Scenario: You have two tables, customers_2025 (customers who shopped in 2025) and customers_2026 (customers who shopped in 2026). You want to find customers who shopped in both years.

Table customers_2025:

Table customers_2026:

SELECT customer_id, customer_name FROM customers_2025
INTERSECT
SELECT customer_id, customer_name FROM customers_2026;

Result:

Only Priya and Arjun appear in both years, so INTERSECT returns only them.

EXCEPT Example:

Scenario: Same two tables. Now you want to find customers who shopped in 2025 but did NOT come back in 2026, basically lost customers.

SELECT customer_id, customer_name FROM customers_2025
EXCEPT
SELECT customer_id, customer_name FROM customers_2026;

Result:

Rahul and Sneha were in 2025 but not in 2026, EXCEPT returns only those rows.

• Order matters in EXCEPT. If you flip the query:

SELECT customer_id, customer_name FROM customers_2026
EXCEPT
SELECT customer_id, customer_name FROM customers_2025;

• You get Kiran (C05), who is new in 2026 but was not in 2025.