SQL Joins

Combines rows from two or more tables:
   Inner Join
   Left Join (Left Outer)
   Right Join (Right Outer)

  Full Outer Join
  Cross Join
  Self Join
7. What is the difference between INNER JOIN and LEFT JOIN?
       INNER JOIN: Returns only matching rows.
       LEFT JOIN: Returns all rows from the left table, and matched rows from the right table (NULL if no match).
8. What is a subquery?
        A query nested inside another query. Can be used in SELECT, FROM, or WHERE clauses.
9. What is a CTE (Common Table Expression)?
        A temporary result set defined with WITH that can be referred to within a SELECT, INSERT, UPDATE, or DELETE.
WITH RecentSales AS (
 SELECT * FROM Sales WHERE SaleDate > '2024-01-01'
)
SELECT * FROM RecentSales;
10. What is the difference between DELETE, TRUNCATE, and DROP?
       DELETE: Removes rows; can use WHERE.
      TRUNCATE: Removes all rows; faster than delete; cannot rollback.
      DROP: Deletes the entire table structure.

11. CROSS JOIN

Returns Cartesian product (all combinations).

SELECT e.emp_name, d.dept_name
FROM employee e
CROSS JOIN department d;
 
12. SELF JOIN
Joining a table to itself.
SELECT e1.emp_name AS Employee, e2.emp_name AS Manager
FROM employee e1
JOIN employee e2
ON e1.manager_id = e2.emp_id;
🔹 Used for hierarchical data
 
ETL / DataStage Mapping
SQL JOIN
DataStage Stage
INNER JOIN
Join Stage
LEFT JOIN
Lookup (Left Outer)
FULL JOIN
Merge Stage
CROSS JOIN
Not recommended