996 SQL Verses Comparisons

IDEABOX:
FOUND ON INTERNET: (Existing Interview Questions!)

Table of Contents

• Full Join VS Inner Join
  • FULL JOIN Explained
  • INNER JOIN Explained
  • Comparison Table
• Self Join VS Inner Join
  • SELF JOIN Explained
  • INNER JOIN Explained
  • Key Differences Table
  • SELF JOIN
  • INNER JOIN
  • Key Differences
• Complex GROUP BY + WHERE OR HAVING SYNTAXES
  • GROUP BY with WHERE and HAVING
    • Syntax Pattern
  • Multiple Conditions (Complex HAVING/WHERE)
  • Notes on Usage
• ANY VS ALL VS IN
  • ANY Operator
  • ALL Operator
  • IN Operator
  • Comparison Table
• SELECT * INTO VS INSERT INTO SELECT
  • SELECT INTO
  • INSERT INTO SELECT
  • Differences Table
• IS NULL VS NULL VS NOT NULL
  • NULL
  • IS NULL and IS NOT NULL
  • NULL in Comparisons
  • Summary Table

Full Join VS Inner Join

A FULL JOIN in SQL returns all rows from both tables, including rows that do not match in either table, filling non-matching columns with NULLs, while an INNER JOIN only returns the rows where there is a match in both tables based on the join condition.[1][3][5]

FULL JOIN Explained

• FULL JOIN (or FULL OUTER JOIN) selects every row from both tables.
• If a row in one table does not have a match in the other, the result shows NULL for that table’s columns.[3][5]
• Useful for combining two tables and capturing all data, even non-matching rows.[4]
• Example syntax:

  SELECT ... FROM table1 FULL JOIN table2 ON table1.column = table2.column;
  

• The output contains all records from both tables; unmatched values appear as NULLs.[2][5]

INNER JOIN Explained

• INNER JOIN returns only the rows that have matching values in both tables.[5][1][3]
• Non-matching rows from either table are excluded.
• Best for finding intersections and only retrieving related data.[6][7]
• Example syntax:

  SELECT ... FROM table1 INNER JOIN table2 ON table1.column = table2.column;
  

• The output contains only matched rows; no NULLs for unmatched data.[1][5]

Comparison Table

Each join serves a different purpose, so the choice depends on whether only matched records or every record from both tables (matched or not) is needed.[4][5][1]

1
2
3
4
5
6
7
8

Self Join VS Inner Join

A SELF JOIN in SQL refers to joining a table to itself to compare or relate rows within the same table, whereas an INNER JOIN typically refers to joining two different tables to find matching records based on a specified condition.[1][2][5]

SELF JOIN Explained

• A SELF JOIN uses the same table twice in a query—each instance is aliased to differentiate columns.[4][6][1]
• Commonly used to reveal relationships within a table, such as employees and their managers (both stored in the same employees table).[5][1]
• Can utilize any join type: INNER, LEFT, etc., but always operates on one table twice.
• Syntax example:

  SELECT e.name AS employee, m.name AS manager
  FROM employees e
  INNER JOIN employees m ON e.manager_id = m.employee_id;
  

• Useful for hierarchical or recursive relationships and for comparing rows.[6][9]

INNER JOIN Explained

• INNER JOIN combines rows from two (or more) tables by returning only matching rows based on the join condition.[2][8]
• Most commonly joins two separate tables, not the same table.[2][4]
• Syntax example:

  SELECT employees.name, departments.dept_name
  FROM employees
  INNER JOIN departments ON employees.dept_id = departments.dept_id;
  

• Best for merging related data across distinct datasets.[8][2]

Key Differences Table

In summary, a SELF JOIN targets relationships within a table, while INNER JOIN usually merges related records across tables.A SELF JOIN in SQL is when a table is joined to itself to find relationships within its own data, while an INNER JOIN combines rows from two tables where there is a match based on specified conditions.[1][5][2]

SELF JOIN

• Joins the same table as two logical entities using aliases.[4][1]
• Useful for hierarchical relationships such as employees and managers in an employee table.[5]
• Syntax example:

  SELECT e.name AS employee, m.name AS manager
  FROM employees e
  INNER JOIN employees m ON e.manager_id = m.employee_id;
  

• Can use INNER JOIN, LEFT JOIN, etc., but always operates on a single table.[1]

INNER JOIN

• Joins two separate tables to match related rows.[8][2]
• Only returns rows where a join condition is satisfied in both tables.
• Syntax example:

  SELECT a.col1, b.col2
  FROM table_a a
  INNER JOIN table_b b ON a.common_field = b.common_field;
  

Key Differences

A SELF JOIN is literally an INNER JOIN (or LEFT JOIN, etc.) carried out on a single table. The real distinction is that INNER JOIN usually means "join across tables," while SELF JOIN means "join within a table" using aliases.[2][5][1]

1
2
3
4
5
6
7
8
9

Complex GROUP BY + WHERE OR HAVING SYNTAXES

Complex SQL queries often combine GROUP BY with WHERE and HAVING clauses to filter data before and after aggregation. The WHERE clause filters rows before grouping, while HAVING filters groups after aggregation—commonly using aggregate functions like COUNT, SUM, or AVG.[1][3][6]

GROUP BY with WHERE and HAVING

• WHERE: Filters rows before grouping; cannot use aggregate functions in WHERE.
• GROUP BY: Organizes rows into groups based on one or more columns.
• HAVING: Filters the aggregated groups; supports aggregate functions.

Syntax Pattern

SELECT column1, aggregate_function(column2)
FROM table_name
WHERE condition
GROUP BY column1
HAVING aggregate_condition;

• Example:

  SELECT department, COUNT(employee_id) AS num_employees, AVG(salary) AS avg_salary
  FROM employees
  WHERE hire_date > '2023-01-01'
  GROUP BY department
  HAVING COUNT(employee_id) > 10 AND AVG(salary) > 50000;
  

  • The WHERE clause filters employees hired after Jan 1, 2023.
  • GROUP BY collects them by department.
  • HAVING only shows departments with more than 10 employees and an average salary above 50,000.[4][6]

Multiple Conditions (Complex HAVING/WHERE)

• Use OR/AND to combine multiple conditions:

  SELECT city, SUM(order_total) AS total_orders
  FROM orders
  WHERE (state = 'CA' OR state = 'NY') AND order_status = 'completed'
  GROUP BY city
  HAVING SUM(order_total) > 10000 OR COUNT(order_id) > 25;
  

• This query:
  • Uses WHERE for pre-group filters on state and status.
  • Applies GROUP BY to city.
  • HAVING applies aggregate and composite conditions on order totals and counts.[3]

Notes on Usage

• HAVING is essential for filtering results of aggregate calculations, as WHERE does not permit aggregates.[6][1]
• WHERE is applied first, then data is grouped, and HAVING filters the result of those groups.[3][4]

Complex GROUP BY queries build nuanced reports or dashboards by cleverly combining WHERE and HAVING filters using AND/OR logic—not just restricting, but shaping how summary insights emerge from raw data.[5][7]

1
2
3
4
5
6
7
8
9

Here is a sample SQL query that combines WHERE, GROUP BY, and HAVING clauses to produce a filtered summary report:[1][2][3]

SELECT department, COUNT(employee_id) AS num_employees, AVG(salary) AS avg_salary
FROM employees
WHERE hire_date >= '2024-01-01'
GROUP BY department
HAVING COUNT(employee_id) > 5 AND AVG(salary) > 40000;

• The WHERE clause filters employees hired since January 1, 2024.
• GROUP BY collects those employees by department.
• HAVING only shows those departments with more than five employees and an average salary greater than 40,000.[2][3][1]

This type of query is often used for analytical summaries where both pre-aggregation and post-aggregation filters are required.

1
2
3

ANY VS ALL VS IN

The SQL operators ANY, ALL, and IN are used to compare a value with a set or subquery results, but each works differently in evaluating conditions.[1][2][4]

ANY Operator

• Returns TRUE if the comparison is TRUE for at least one value in the set returned by a subquery.
• Syntax:

  WHERE column operator ANY (subquery)
  

• Example:

  WHERE salary > ANY (SELECT salary FROM employees WHERE department = 'HR')
  

• Meaning: Finds rows where salary is greater than at least one HR employee’s salary.[5][6][1]

ALL Operator

• Returns TRUE if the comparison is TRUE for every value in the set from a subquery.
• Syntax:

  WHERE column operator ALL (subquery)
  

• Example:

  WHERE salary > ALL (SELECT salary FROM employees WHERE department = 'HR')
  

• Meaning: Finds rows where salary is greater than every HR employee’s salary.[2][4][1]

IN Operator

• Returns TRUE if the value matches any value in a given list or subquery (effectively an “equals ANY” check).
• Syntax:

  WHERE column IN (value1, value2, ...)
  WHERE column IN (SELECT ... )
  

• Example:

  WHERE department IN ('HR', 'Finance')
  

• Meaning: Finds rows where department matches any listed department, or any value from a subquery.[7][9]

Comparison Table

ANY and ALL are flexible for multiple comparison operators, while IN is equivalent to “= ANY” and is limited to equality checks.[4][1][7]

1
2
3
4
5
6
7
8
9
10

SELECT * INTO VS INSERT INTO SELECT

SELECT * INTO creates a new table and copies data from an existing table, while INSERT INTO SELECT inserts data into an already existing table from the results of a SELECT query[1][3][5].

SELECT INTO

• Use when the destination table does not exist.
• Automatically creates the new table with a schema matching the selected columns.
• Example:

  SELECT * INTO new_table FROM old_table WHERE condition;
  

• Good for quick backups, creating temp/intermediate tables, or exporting results.[3][5]
• The new table has no indexes, constraints, or keys unless explicitly added after creation.[1][2][3]

INSERT INTO SELECT

• Use when the destination table already exists.
• Inserts data rows from one table or query into the already defined target table.
• Example:

  INSERT INTO existing_table (column1, column2)
  SELECT column1, column2 FROM source_table WHERE condition;
  

• Existing records in the target table are preserved; only new rows are added.[5][7][1]

Differences Table

In summary, SELECT INTO is ideal for creating new tables from queries or subsets, while INSERT INTO SELECT is best for moving or copying data when the structure is pre-defined.[1][3][5]

1
2
3
4
5
6
7
8
9

IS NULL VS NULL VS NOT NULL

In SQL, IS NULL, NULL, and IS NOT NULL have specific meanings and uses. NULL is a marker indicating a missing value, IS NULL is used to check for such missing values, and IS NOT NULL checks for the presence of a value.[2][7][8]

NULL

• NULL is not a value but a special marker to show that data is missing or unknown.[5][7]
• NULL is different from zero (0), an empty string (""), or spaces—it means no value at all.[7][8][2]
• Used in INSERT/UPDATE statements to indicate "no value" in a column.

IS NULL and IS NOT NULL

• To test for missing values, use the IS NULL operator:

  SELECT * FROM employees WHERE department IS NULL;
  

  This query returns records where the department column is missing a value.[8][2]
• IS NOT NULL is used to find records where a value exists:

  SELECT * FROM employees WHERE department IS NOT NULL;
  

  This query returns rows where department has any value except NULL.[2][8]

NULL in Comparisons

• Using = NULL or <> NULL does not work for filtering rows, because NULL cannot be compared using standard operators (since NULL means "unknown").[3][9][2]
• Always use IS NULL or IS NOT NULL for comparisons in WHERE clauses.[9][8][2]

Summary Table

In summary: use IS NULL/IS NOT NULL to filter records with or without values, and remember that NULL means "missing" or "unknown," not empty or zero.[7][8][2]

1
2
3
4
5
6
7
8
9