MySQL Table Relationships and Joins Explained With Code

Table Relationships

Tables are used to hold data in relational databases. With its rows and columns, a table is conceptually comparable to a spreadsheet. The organization of data into distinct, connected tables is a fundamental feature of relational databases. By using this method, data redundancy the repetition of the same information in the database is avoided. Because modifications would need to be made in several locations, redundancy consumes store space and complicates data maintenance.

In a relational database, common columns are used to link tables together. Primary Keys and Foreign Keys are the most often used methods for creating and enforcing these associations.

• An attribute, or collection of attributes, in a table that uniquely identifies every row in that table is called a primary key. There should be a primary key in each database table. Every entry must have a distinct value stored in the main key. To refer to a particular table row, primary keys are essential.
• An attribute that refers to the Primary Key of another table (the referred table) from one table (the referencing table) is called a foreign key. To connect tables and create a relationship often called a parent-child relationship foreign keys are utilized. In order to maintain referential integrity, a foreign key constraint requires that values in the child table’s foreign key column match values in the parent table’s primary key column.

Here is an example demonstrating how to create two related tables, parent and child, using Primary and Foreign Keys:

-- Create the parent table
CREATE TABLE parent (
    id INT NOT NULL,
    PRIMARY KEY (id)
); -- Assuming ENGINE=INNODB for foreign key support
-- Create the child table with a foreign key referencing the parent table
CREATE TABLE child (
    id INT,
    parent_id INT,
    INDEX par_ind (parent_id), -- Creating an index on the FK is often good practice 
    FOREIGN KEY (parent_id)        REFERENCES parent(id)
); -- Assuming ENGINE=INNODB for foreign key support 

You can add data once the tables have been created. Because of the foreign key requirement, if you were to insert a parent_id into the child table, the id value would have to already be present in the parent table.

Combining Data from Multiple Tables using JOIN Operations

Although dividing data into several related tables (such as pet and event in the tutorial example) helps with organization and reduces redundancy, you frequently need to mix data from these several tables to provide a comprehensive view of relevant information. JOIN operations become crucial at this point.

JOINs let you collect data from multiple tables. You must specify the tables and a join condition to inform the database how to match rows from one table to another. The join condition compares data in related columns, generally the Foreign Key and Primary Key of two tables.

MySQL will return a Cartesian product if you query several tables in a single SELECT statement without a join condition. This indicates that all of the rows from the first and second tables are combined. Usually, this leads to a lot of unnecessary records. You should always use a correct JOIN condition to prevent this.

JOIN clauses of several kinds are supported by MySQL, including:

INNER JOIN: The most popular kind of join is the inner join. Based on the join condition, it generates a collection of records with values that match in both tables. Only when the ON clause’s condition is satisfied in both tables will rows from either table show up in the result. The related row is excluded from the result if a value appears in one table’s join column but not in the other.

Let’s demonstrate an INNER JOIN using the pet and event tables from the tutorial example. Birth dates from the pet table and litter dates from the event table are required to determine the ages at which pets had litters.

SELECT pet.name,
       TIMESTAMPDIFF(YEAR,birth,date) AS age, -- Calculate age in years 
       remark
FROM pet -- From the pet table
INNER JOIN event -- Join with the event table
  ON pet.name = event.name -- Match rows where the pet name is the same in both tables 
WHERE event.type = 'litter'; -- Filter for 'litter' events 

Because the field name appears in both tables, this query uses pet.name and event.name to indicate which table’s name column is being referenced to. The join condition is the ON pet.name = event.name clause. If the event type is ‘litter’ and the pet and event tables have names that match, this query would only return entries for those pets.

LEFT JOIN: This kind of join yields the matching records from the right table as well as all of the records from the left table. The columns from the right table will show up in the result set as NULL if, according to the join condition, a row from the left table does not match in the right table. Every record from the “left” side of the join will be included in the outcome, according to an LEFT JOIN.

The tutorials_tbl (left table) and tcount_tbl (right table), connected by tutorial_author, are two hypothetical tables sample that we will examine. While tcount_tbl may list authors and a count of their tutorials, tutorials_tbl lists tutorials and authors.

-- Hypothetical table data (simplified for clarity, based on )
-- tutorials_tbl:
-- tutorial_id | tutorial_title | tutorial_author | submission_date
-- ----------- | -------------- | --------------- | ---------------
-- 1           | Learn PHP      | John Poul       | 2007-05-24
-- 2           | Learn MySQL    | Abdul S         | 2007-05-24
-- 3           | JAVA Tutorial  | Sanjay          | 2007-05-21
-- tcount_tbl:
-- tutorial_author | tutorial_count
-- --------------- | --------------
-- John Poul       | 1
-- Sanjay          | 1
SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count
FROM tutorials_tbl AS a -- 'a' is an alias for tutorials_tbl (the left table) 
LEFT JOIN tcount_tbl AS b -- 'b' is an alias for tcount_tbl (the right table) 
  ON a.tutorial_author = b.tutorial_author; -- Join condition

Using the sample data above, the LEFT JOIN would produce the following result:

-- Result of the LEFT JOIN
-- tutorial_id | tutorial_author | tutorial_count
-- ----------- | --------------- | --------------
-- 1           | John Poul       | 1
-- 2           | Abdul S         | NULL          -- Abdul S has no match in tcount_tbl
-- 3           | Sanjay          | 1

Observe that even though there is no matching tutorial_author in the right table (tcount_tbl), Abdul S appears in the result because he is in the left table (tutorials_tbl), which causes the tutorial_count column to be NULL.

Table names are sometimes aliased (AS) to simplify queries, especially when combining tables. You must precede columns with the table name or its alias (e.g., pet.name or a.tutorial_author) to identify them in connected tables with the same name. In production code, it’s best to list the columns you need rather than use SELECT * to fetch all columns.

RIGHT JOIN: All of the records from the right table and the corresponding records from the left table are returned via a RIGHT JOIN. NULL values are returned for the columns from the left table if there isn’t a match. Though the way the connected tables are handled is different, it is comparable to an LEFT JOIN.

The syntax is:

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

For example, to list all departments and any employees assigned to them (even if a department has no employees):

SELECT d.name, e.first_name, e.last_name
FROM employees AS e
RIGHT JOIN departments AS d
ON d.department_id = e.department_id;

CROSS JOIN: Every record from both tables is returned using a CROSS JOIN. Each row in the first table is multiplied by each row in the second table to create a collection of related rows. A Cartesian product of the two tables is the outcome of this. Listing tables in the FROM clause, separated by commas, without a WHERE clause or ON condition, will implicitly create a CROSS JOIN.

The syntax is:

SELECT column_name(s)
FROM table1
CROSS JOIN table2;

For example, to list every possible combination of each employee with each department:

SELECT e.first_name, e.last_name, d.name
FROM employees AS e
CROSS JOIN departments AS d;

CROSS JOINs can produce very large result sets and should be used cautiously.

Self Join: When a table is joined with itself, it’s called a self join. Finding employees and their managers when they are both recorded in the same employees database requires this in order to relate data within a single table. Aliases are required to differentiate between the two instances of the table while performing a Self Join.

The syntax using JOIN with aliases is:

SELECT t1.column_name, t2.column_name
FROM table_name AS t1
JOIN table_name AS t2
ON t1.common_column = t2.common_column;

For example, to list employees and their managers from a single employees table (assuming a manager_id column links to the id column in the same table):

SELECT employee.first_name, employee.last_name, manager.first_name AS manager_first_name
FROM employees AS employee
JOIN employees AS manager
ON employee.manager_id = manager.employee_id; -- Assuming employee_id is the primary key

Aliases: Within a query, aliases are short-term names assigned to a table or column. They simplify table names or make column names easier to comprehend, especially in intricate queries that involve several tables or functions. Aliases are only there while that particular query is running. Although it is frequently unnecessary for table aliases, the AS keyword is needed to construct an alias.

For columns:

SELECT column_name AS alias_name
FROM table_name;

For tables (useful in joins to shorten names or when joining a table to itself):

SELECT alias1.column_name, alias2.column_name
FROM table_name AS alias1
JOIN table_name AS alias2
ON alias1.join_column = alias2.join_column;

For example, SELECT e.first_name FROM employees AS e; uses e as an alias for the employees table.