← Public decks·Computer Science

SQL JOIN Operations and the First Three Normal Forms of Database Design

INNER, OUTER, and other JOIN semantics plus 1NF, 2NF, and 3NF rules with concrete examples of normalization to remove redundancy and update anomalies.

9 cards· by GuruOwl

Make a deck like this from your own PDF — free.

Try it
  1. 01
    An INNER JOIN returns only rows where the join condition matches in both tables, excluding non-matching rows.
    This is the most common join type used in SQL.
    Flashify::SQL::JOINs
  2. 02
    A LEFT OUTER JOIN returns all rows from the left table and supplies NULLs for non-matching rows from the right table.
    This ensures no data from the primary table is lost during the join.
    Flashify::SQL::JOINs
  3. 03
    A CROSS JOIN produces the Cartesian product, where every row of the left table is paired with every row of the right table.
    It is rarely used in production without specific WHERE filters due to the massive result sets it generates.
    Flashify::SQL::JOINs
  4. 04
    A SELF JOIN is a table joined to itself, typically used for hierarchical data like employee-manager relationships.
    Aliases are required to distinguish the two instances of the same table.
    Flashify::SQL::JOINs
  5. 05
    First normal form (1NF) requires that every column contain only atomic values and that each row be unique.
    Storing comma-separated values in a single column is a classic violation of 1NF.
    Flashify::DatabaseNormalization
  6. 06
    To satisfy Second normal form (2NF), a table must be in 1NF and every non-key attribute must depend on the entire primary key.
    This specifically addresses issues with composite primary keys.
    Flashify::DatabaseNormalization
  7. 07
    Third normal form (3NF) requires that no non-key attribute depends transitively on the primary key.
    If A determines B and B determines C, C's dependency on A is transitive.
    Flashify::DatabaseNormalization
  8. 08
    What are the three types of anomalies eliminated by proper database normalization?
    1. Insertion anomalies 2. Deletion anomalies 3. Update anomalies
    Normalization ensures data integrity by preventing these logical inconsistencies.
    Flashify::DatabaseNormalization
  9. 09
    Denormalization is the intentional introduction of redundancy to improve read performance in data warehouses.
    It trades off storage and write-integrity for faster query execution.
    Flashify::DatabaseNormalization