Scribd
Upload
33 views9 pages

Lecture 7 SQL Join

The document explains the concept of FOREIGN KEY constraints in SQL, detailing how they link two tables by referencing a PRIMARY KEY from another table. It provides examples of creating and altering tables with FOREIGN KEY constraints, as well as methods for dropping these constraints. Additionally, it covers SQL JOIN types, including Inner, Left, Right, and Full Joins, with syntax and examples for each type to illustrate how to combine data from multiple tables.

Uploaded by

Rio Manuel Valente
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
33 views9 pages

Lecture 7 SQL Join

The document explains the concept of FOREIGN KEY constraints in SQL, detailing how they link two tables by referencing a PRIMARY KEY from another table. It provides examples of creating and altering tables with FOREIGN KEY constraints, as well as methods for dropping these constraints. Additionally, it covers SQL JOIN types, including Inner, Left, Right, and Full Joins, with syntax and examples for each type to illustrate how to combine data from multiple tables.

Uploaded by

Rio Manuel Valente
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 9

Foreign Key

SQL FOREIGN KEY Constraint


A FOREIGN KEY is a key used to link two tables together.

A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table.

The table containing the foreign key is called the child table, and the table containing the candidate key is called the
referenced or parent table.

Look at the following two tables:

"Persons" table:

PersonID LastName FirstName Age


1 Hansen Ola 30
2 Svendson Tove 23
3 Pettersen Kari 20

"Orders" table:

OrderID OrderNumber PersonID


1 77895 3
2 44678 3
3 22456 2
4 24562 1

SQL FOREIGN KEY on CREATE TABLE


The following SQL creates a FOREIGN KEY on the "PersonID" column when the "Orders" table is created:

MySQL:

CREATE TABLE Orders (


OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);

SQL Server / Oracle / MS Access:

CREATE TABLE Orders (


OrderID int NOT NULL PRIMARY KEY,
OrderNumber int NOT NULL,
PersonID int FOREIGN KEY REFERENCES Persons(PersonID)
);

Database Management System Kenrick Agustin S. Secugal


University of Antique - College of Computer Studies Reference: http://www.sql-join.com/sql-join-types
www.w3schools.com/sql/sql_foreignkey.asp
To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the
following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Orders (


OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)
REFERENCES Persons(PersonID)
);

SQL FOREIGN KEY on ALTER TABLE


To create a FOREIGN KEY constraint on the "PersonID" column when the "Orders" table is already created, use the
following SQL:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Orders


ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the
following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Orders


ADD CONSTRAINT FK_PersonOrder
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
DROP a FOREIGN KEY Constraint

To drop a FOREIGN KEY constraint, use the following SQL:

MySQL:

ALTER TABLE Orders


DROP FOREIGN KEY FK_PersonOrder;
SQL Server / Oracle / MS Access:

ALTER TABLE Orders


DROP CONSTRAINT FK_PersonOrder;

Database Management System Kenrick Agustin S. Secugal


University of Antique - College of Computer Studies Reference: http://www.sql-join.com/sql-join-types
www.w3schools.com/sql/sql_foreignkey.asp
SQL JOIN
A JOIN clause is used to combine a rows from two or more tables, based on related column between them.
Four Basic types:
1. Inner Join
Select all records from Table A and Table B where the join condition is met

2. Left Join
Select all records from Table A, along with records from Table B for which the join condition is met (if at all).

3. Right Join
Select all records from Table B, along with records from Table A for which the join condition is met (if at all).

4. Full Join
Select all records from Table A and Table B, regardless of whether the join condition is met or not

Database Management System Kenrick Agustin S. Secugal


University of Antique - College of Computer Studies Reference: http://www.sql-join.com/sql-join-types
www.w3schools.com/sql/sql_foreignkey.asp
Examples of SQL Join Types

Let's use the tables we introduced in the “What is a SQL join?” section to show examples of these joins in action.
The relationship between the two tables is specified by the customer_id key, which is the "primary key" in
customers table and a "foreign key" in the orders table:

Database Management System Kenrick Agustin S. Secugal


University of Antique - College of Computer Studies Reference: http://www.sql-join.com/sql-join-types
www.w3schools.com/sql/sql_foreignkey.asp
Inner Join

Let’s say we wanted to get a list of those customers who placed an order and the details of the order they placed. This
would be a perfect fit for an inner join, since an inner join returns records at the intersection of the two tables.

Syntax

select first_name, last_name, order_date, order_amount


from customers c
inner join orders o
on c.customer_id = o.customer_id;

first_name last_name order_date order_amount


George Washington 07/4/1776 $234.56
John Adams 05/23/1784 $124.00
Thomas Jefferson 03/14/1760 $78.50
Thomas Jefferson 09/03/1790 $65.50

Note that only George Washington, John Adams and Thomas Jefferson placed orders, with Thomas Jefferson
placing two separate orders on 3/14/1760 and 9/03/1790.

Left Join

If we wanted to simply append information about orders to our customers table, regardless of whether a customer
placed an order or not, we would use a left join. A left join returns all records from table A and any matching records
from table B.

Syntax

select first_name, last_name, order_date, order_amount


from customers c
left join orders o
on c.customer_id = o.customer_id

first_name last_name order_date order_amount


George Washington 07/04/1776 $234.56
John Adams 05/23/1784 $124.00
Thomas Jefferson 03/14/1760 $78.50
Thomas Jefferson 09/03/1790 $65.50
James Madison NULL NULL
James Monroe NULL NULL

Database Management System Kenrick Agustin S. Secugal


University of Antique - College of Computer Studies Reference: http://www.sql-join.com/sql-join-types
www.w3schools.com/sql/sql_foreignkey.asp
Note that since there were no matching records for James Madison and James Monroe in our orders table, the
order_date and order_amount are NULL, which simply means there is no data for these fields.

Or

Add null like the query below:

select first_name, last_name, order_date, order_amount


from customers c
left join orders o
on c.customer_id = o.customer_id
where order_date is NULL

Right Join

Right join is a mirror version of the left join and allows to get a list of all orders, appended with customer information.

Syntax

select first_name, last_name, order_date, order_amount


from customers c
right join orders o
on c.customer_id = o.customer_id

first_name last_name order_date order_amount


George Washington 07/04/1776 $234.56
Thomas Jefferson 03/14/1760 $78.50
John Adams 05/23/1784 $124.00
Thomas Jefferson 09/03/1790 $65.50
NULL NULL 07/21/1795 $25.50
NULL NULL 11/27/1787 $14.40

Note that since there were no matching customer records for orders placed in 1795 and 1787, the first_name
and last_name fields are NULL in the resulting set.

Also note that the order in which the tables are joined is important. We are right joining the orders table to the
customers table. If we were to right join the customers table to the orders table, the result would be the same
as left joining the orders table to the customers table.

Why is this useful? Simply adding a “where first_name is NULL” line to our SQL query returns a list of all orders
for which we failed to record information about the customers who placed them:

Database Management System Kenrick Agustin S. Secugal


University of Antique - College of Computer Studies Reference: http://www.sql-join.com/sql-join-types
www.w3schools.com/sql/sql_foreignkey.asp
OR this syntax will help

select first_name, last_name, order_date, order_amount


from customers c
right join orders o
on c.customer_id = o.customer_id
where first_name is NULL

Full Join

Finally, for a list of all records from both tables, we can use a full join.

Syntax

select first_name, last_name, order_date, order_amount


from customers c
full join orders o
on c.customer_id = o.customer_id

first_name last_name order_date order_amount


George Washington 07/04/1776 $234.56
Thomas Jefferson 03/14/1760 $78.50
John Adams 05/23/1784 $124.00
Thomas Jefferson 09/03/1790 $65.50
NULL NULL 07/21/1795 $25.50
NULL NULL 11/27/1787 $14.40
James Madison NULL NULL
James Monroe NULL NULL

**The issue is that XAMPP uses MySQL (or MariaDB), which does not support FULL OUTER JOIN directly.

Database Management System Kenrick Agustin S. Secugal


University of Antique - College of Computer Studies Reference: http://www.sql-join.com/sql-join-types
www.w3schools.com/sql/sql_foreignkey.asp
Another example of Full SQL mode:

CREATE TABLE departments (


dept_id INT PRIMARY KEY,
dept_name VARCHAR(50),
location VARCHAR(50)
);

-- Create the employees table


CREATE TABLE employees (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(50),
age INT,
salary DECIMAL(10, 2),
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

-- Insert data into departments


INSERT INTO departments (dept_id, dept_name, location) VALUES
(1, 'Human Resources', 'New York'),
(2, 'Engineering', 'San Francisco'),
(3, 'Marketing', 'Chicago'),
(4, 'Sales', 'Los Angeles');

-- Insert data into employees


INSERT INTO employees (emp_id, emp_name, age, salary, dept_id) VALUES
(101, 'Alice', 30, 70000.00, 2),
(102, 'Bob', 28, 65000.00, 2),
(103, 'Charlie', 35, 80000.00, 1),
(104, 'David', 40, 90000.00, 3),
(105, 'Eva', 29, 72000.00, 4),
(106, 'Frank', 33, 68000.00, 3),
(107, 'Grace', 27, 60000.00, 4),
(108, 'Hank', 36, 85000.00, 1),
(109, 'Ivy', 31, 75000.00, NULL),
(110, 'Jack', 45, 95000.00, NULL);

-- INNER JOIN: Employees with departments


SELECT emp_name, age, salary, dept_name, location
FROM employees
INNER JOIN departments
ON employees.dept_id = departments.dept_id;

Database Management System Kenrick Agustin S. Secugal


University of Antique - College of Computer Studies Reference: http://www.sql-join.com/sql-join-types
www.w3schools.com/sql/sql_foreignkey.asp
-- LEFT JOIN: All employees with departments (even if no department assigned)
SELECT emp_name, age, salary, dept_name, location
FROM employees
LEFT JOIN departments
ON employees.dept_id = departments.dept_id;

-- RIGHT JOIN: All departments with employees (even if no employees assigned)


SELECT emp_name, age, salary, dept_name, location
FROM employees
RIGHT JOIN departments
ON employees.dept_id = departments.dept_id;

-- FULL OUTER JOIN (MySQL-compatible): Combines LEFT and RIGHT joins using UNION
SELECT emp_name, age, salary, dept_name, location
FROM employees
LEFT JOIN departments
ON employees.dept_id = departments.dept_id
UNION
SELECT emp_name, age, salary, dept_name, location
FROM employees
RIGHT JOIN departments
ON employees.dept_id = departments.dept_id;

Database Management System Kenrick Agustin S. Secugal


University of Antique - College of Computer Studies Reference: http://www.sql-join.com/sql-join-types
www.w3schools.com/sql/sql_foreignkey.asp

You might also like