joins in mysql

🔗 What Are JOINS?

JOINS allow you to combine data from two or more tables based on a related column.
Think of it as building relationships between tables in a relational database to retrieve meaningful combined data.

🧠 Key Concepts:

Relational databases store related data across multiple tables.
JOINS use common fields (like emp_no) to connect rows that are logically related.
You can retrieve columns from multiple tables in a single query.

🏛️ Example Tables:

employees → has emp_no, first_name, last_name
dept_emp → has emp_no, dept_no, from_date

You can JOIN them using emp_no to get:

Employee number
Name
Department number
Start date of their department role

⚠️ Important Notes:

The columns used to join must be of the same type and refer to the same concept (e.g., employee ID).
Tables don’t have to be logically adjacent in the schema — you can JOIN any tables as long as there’s a related field.

🧰 Coming Up:

Next lecture covers INNER JOIN syntax and behavior.
You'll also get to practice JOINs using duplicate tables you’ll create (departments_dup, dept_manager_dup).

task:If you currently have the ‘departments_dup’ table set up, use DROP COLUMN to remove the ‘dept_manager’ column from the ‘departments_dup’ table.

Then, use CHANGE COLUMN to change the ‘dept_no’ and ‘dept_name’ columns to NULL.

(If you don’t currently have the ‘departments_dup’ table set up, create it. Let it contain two columns: dept_no and dept_name. Let the data type of dept_no be CHAR of 4, and the data type of dept_name be VARCHAR of 40. Both columns are allowed to have null values. Finally, insert the information contained in ‘departments’ into ‘departments_dup’.)

Then, insert a record whose department name is “Public Relations”.

Delete the record(s) related to department number two.

Insert two new records in the “departments_dup” table. Let their values in the “dept_no” column be “d010” and “d011”.

# if you currently have ‘departments_dup’ set up:

ALTER TABLE departments_dup

DROP COLUMN dept_manager;



ALTER TABLE departments_dup

CHANGE COLUMN dept_no dept_no CHAR(4) NULL;



ALTER TABLE departments_dup

CHANGE COLUMN dept_name dept_name VARCHAR(40) NULL;



# if you don’t currently have ‘departments_dup’ set up

DROP TABLE IF EXISTS departments_dup;

CREATE TABLE departments_dup

(

    dept_no CHAR(4) NULL,

    dept_name VARCHAR(40) NULL

);



INSERT INTO departments_dup

(

    dept_no,

    dept_name

)SELECT

                *

FROM

                departments;



INSERT INTO departments_dup (dept_name)

VALUES                ('Public Relations');



DELETE FROM departments_dup

WHERE

    dept_no = 'd002'; 



INSERT INTO departments_dup(dept_no) VALUES ('d010'), ('d011');

🔗 INNER JOIN: The Basics

An INNER JOIN returns only matching records from both tables based on a common column.
Think of it as the overlap in a Venn diagram.

🧠 When to Use INNER JOIN

When you want rows that exist in both tables based on a shared key.
Non-matching rows (i.e., nulls or unmatched values) are excluded from the result.

🏛️ Example Tables Used:

dept_manager_dup (left table)
departments_dup (right table)
Join column: dept_no (department number)

🔹 INNER JOIN Syntax:

SELECT t1.column1, t2.column2, ...
FROM dept_manager_dup t1
JOIN departments_dup t2
ON t1.dept_no = t2.dept_no;

t1 and t2 are table aliases to simplify your code.
ON t1.dept_no = t2.dept_no tells SQL how to match rows.

✅ Best Practices:

Always qualify column names with their table alias (t1.column, t2.column) to avoid confusion.
Use aliases to keep queries readable—especially with multiple JOINs.

🔍 Recap of Tables’ Data:

Some records in both tables lack department numbers or have null values → These will not appear in INNER JOIN results.

🔴 Summary of INNER JOIN Behavior:

Match Exists	Included in Result?
✅ Yes	✅ Yes
❌ No	❌ No

🧾 Goal of the Query:

Extract a list of:

dept_no (Department Number)
emp_no (Employee Number)
dept_name (Department Name)

From:

dept_manager_dup (aliased as M)
departments_dup (aliased as D)

✅ INNER JOIN Query Used:

SELECT M.dept_no, M.emp_no, D.dept_name
FROM dept_manager_dup M
INNER JOIN departments_dup D
ON M.dept_no = D.dept_no
ORDER BY M.dept_no;

🔍 What This Query Does:

Matches records from both tables where dept_no values are the same
Retrieves only rows where this match exists
Excludes:
- Any rows with NULL values in dept_no
- Departments that exist in one table but not the other

🧠 Key Points from the Analysis:

INNER JOIN acts like the overlap in a Venn diagram
It excludes NULLs and non-matching values
In this example:
- Departments 1, 2, 10, and 11 were missing in the final output
- NULL department numbers were also excluded
Final result showed only matching department numbers (3–9) → 20 rows total

📌 Reminder:

INNER JOIN = "Show me only what both tables have in common"

task:Extract a list containing information about all managers’ employee number, first and last name, department number, and hire date.

SELECT

    e.emp_no,

    e.first_name,

    e.last_name,

    dm.dept_no,

    e.hire_date

FROM

    employees e

        JOIN

    dept_manager dm ON e.emp_no = dm.emp_no;

🧠 Key Notes on Using JOINs (Not Just INNER JOINs):

✅ 1. Select Any Columns You Want

You can select any combination of columns from the joined tables.
Just make sure the column is from one of the tables involved in the JOIN.

Example (adding contract dates):

SELECT M.dept_no, M.emp_no, D.dept_name, M.from_date, M.to_date
FROM dept_manager_dup M
JOIN departments_dup D
ON M.dept_no = D.dept_no;

✅ 2. Table Aliases Can Be Declared Early

Some people start with the FROM and JOIN section to define aliases early.
This can help with readability—especially when joining multiple tables.

Example:

FROM dept_manager_dup M
JOIN departments_dup D
ON M.dept_no = D.dept_no

Then use M and D in the SELECT clause.

✅ 3. `INNER` Keyword is Optional

JOIN = INNER JOIN by default.
Practitioners often omit the word INNER to write cleaner code.

JOIN departments_dup D  -- Same as INNER JOIN

✅ 4. Matching Column Order Doesn’t Matter

M.dept_no = D.dept_no  
-- is the same as --
D.dept_no = M.dept_no

It’s just about your preference and readability.

✅ 5. Be Careful with `ORDER BY` in Larger Queries

If two tables share the same column name (e.g., dept_no), it's better to specify the table alias:

ORDER BY M.dept_no

This becomes crucial in larger datasets and with tools like procedures or indexes.

💡 Final Tip:

Always write clear, readable JOINs, especially as you work with more complex databases.

📌 What Are Duplicate Records?

Duplicate rows = rows where all column values are identical.
Common in raw or uncontrolled data.
Best practice is to avoid or clean them, but sometimes they exist.

🧪 Example in Practice:

Two duplicates were manually added:

A duplicate manager row with emp_no = 110228, dept_no = 3
A duplicate department entry for dept_no = 9 (Customer Service)

🎯 Effect on JOIN Output:

Before duplicates: 20 rows
After duplicates: 25 rows

Why the increase?

One duplicate manager → +1 row
One duplicate department → causes each related employee to match twice, adding +4 rows

✅ How to Handle Duplicates in Query Results:

Use `GROUP BY`:

SELECT M.emp_no, D.dept_name
FROM dept_manager_dup M
JOIN departments_dup D ON M.dept_no = D.dept_no
GROUP BY M.emp_no;

Why GROUP BY M.emp_no?
- Because emp_no is the field that differs most and uniquely identifies managers.
- This removes the effect of duplicated rows by collapsing results per unique employee.

🧠 Best Practice Reminder:

Never assume your data is clean—especially with large datasets.
Use GROUP BY proactively when duplicate outputs are not desirable.

🔗 What Is a LEFT JOIN?

A LEFT JOIN returns:
- All matching rows from both tables (just like INNER JOIN)
- Plus all unmatched rows from the left table, with NULL values for missing matches on the right

🔵 Think of a Venn diagram:

The red area = what INNER JOIN shows (the overlap)
The blue area = the rest of the left table → only LEFT JOIN includes this

🔍 Example Setup:

Tables used:
- dept_manager_dup (aliased as M)
- departments_dup (aliased as D)
Related by: dept_no

✅ LEFT JOIN Syntax:

SELECT M.dept_no, M.emp_no, D.dept_name
FROM dept_manager_dup M
LEFT JOIN departments_dup D
ON M.dept_no = D.dept_no;

🧪 Result Analysis:

INNER JOIN returned 20 rows
LEFT JOIN returned 26 rows → 6 additional non-matching rows from the left table

The 6 extra rows include:

4 records from M with dept_no values that don’t exist in D
- These show NULL in the dept_name field
2 records with dept_no = 2 from M, but no match in D
- These also show NULL for dept_name

⚠️ Important Reminder:

LEFT JOIN is directional. The table on the left determines what "non-matching" rows get included.

Switching M and D in the join will change the result!

💬 In Simple Terms:

INNER JOIN → only what matches
LEFT JOIN → all from the left, matches from the right, and NULL if no match

task:Join the 'employees' and the 'dept_manager' tables to return a subset of all the employees whose last name is Markovitch. See if the output contains a manager with that name.

Hint: Create an output containing information corresponding to the following fields: ‘emp_no’, ‘first_name’, ‘last_name’, ‘dept_no’, ‘from_date’. Order by 'dept_no' descending, and then by 'emp_no'.

SELECT

    e.emp_no,  

    e.first_name,  

    e.last_name,  

    dm.dept_no,  

    dm.from_date  

FROM  

    employees e  

        LEFT JOIN   



dept_manager dm ON e.emp_no = dm.emp_no  

WHERE  

    e.last_name = 'Markovitch'  

ORDER BY dm.dept_no DESC, e.emp_no;

Command Palette