Joining Tables

• PRIMARY KEYS: A primary key is used to ensure data in the specific column is unique. It is a column cannot have NULL values. It is either an existing table column or a column that is specifically generated by the database according to a defined sequence.
• FOREIGN KEYS: A foreign key is a column or group of columns in a relational database table that provides a link between data in two tables. It is a column (or columns) that references a column (most often the primary key) of another table.

• INNER JOIN

  Returns matching values in both tables

  • Example:

      SELECT 
          album_id, 
          title, 
          album.artist_id, 
          name as artist_name
      FROM album
      INNER JOIN artist ON artist.artist_id = album.artist_id
      WHERE albumm.artist_id = 1;
    

    Syntax for INNER JOIN

      SELECT 
          table_A.columnX,
          table_A.columnY,
          table_B.columnZ
      FROM table_A
      INNER JOIN table_B ON table_A.foreign_key = table_B.primary_key;
    

    Syntax for MULTIPLE INNER JOIN

      SELECT 
          table_A.columnX,
          table_A.columnY,
          table_B.columnZ,
          table_C columnW
      FROM table_A
      INNER JOIN table_B on table_B.foreign_key = table_A.primary_key
      INNER JOIN table_C on table_C.foreign_key = table_B.primary_key;
    

• LEFT JOIN

  The LEFT JOIN keyword returns all records from the left table (table1), and the matching records from the right table (table2). The result is 0 records from the right side, if there is no match.

  • Example:

      SELECT 
          Admitted.Pattiend_ID,
          Admitted,
          Discharged
      FROM Admitted
      LEFT JOIN Discharged ON Discharged.Patient_ID = Admitted.Patient_ID;
    

• RIGHT JOIN

  The RIGHT JOIN keyword returns all records from the right table (table2), and the matching records from the left table (table1). The result is 0 records from the left side, if there is no match.

  • Example:

      SELECT 
          Admitted.Pattiend_ID,
          Admitted,
          Discharged
      FROM Admitted
      RIGHT JOIN Discharged ON Discharged.Patient_ID = Admitted.Patient_ID;
    

Summary

• INNER JOIN: Only returns matching rows in both tables
• LEFT JOIN (or RIGHT JOIN): All rows from the main table plus match table
• NULL: Displayed if no match is found
• LEFT JOIN and RIGHT JOIN can be interchangeable

• UNION

  THE UNION operator is used to combine the result-set of two or more SELECT statements.

  • Every SELECT statemnt within UNION must have the same number of columns.
  • The columns must also have similar data types.
  • The columns in every SELECT statement must also be in the same order

  • Example:

      SELECT
      album_id,
      title,
      artist_id
      FROM album
      WHERE artist_id IN (1,3)
      UNION
      SELECT
      album_id,
      title,
      artisti_id
      FROM album
      WHERE artist_id IN (1,4,5);
    

• UNION ALL

  THE UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL.

  Syntax

        SELECT column_name(s) FROM table1
        UNION ALL
        SELECT column_name(s) FROM table2;
  

  • Example:

      SELECT 
      album_id,
      title,
      artist_id
      FROM album
      WHERE artist_id IN (1,3)
      UNION ALL
      SELECT 
      album_id,
      title,
      artist_id
      FROM album
      WHERE artist_id IN (1,4,5);
    

Summary

• UNION or UNION ALL: Combines queries from the same table or different tables.
• If combining data from different tables:
  • Select the same number of columns in the same order
  • Columns should have the same data types
• If source tables have different column names
  • Alias the column names UNION: Discards duplicates UNION ALL: Includes duplicates