Skip to content
Author: Tianle Yuan

** Multiple Table Operation**⚓︎

Glance of contents⚓︎

Here are some higher-level uses:

  • Joins - Basic Join Concept.
  • Inner Join - Inner join two different tables.
  • Left Join - Left join two different tables.
  • Right Join - Right join two different tables.
  • Full Join - Outer join two different tables.
  • Self Join - Inner join a table with itself.
  • Union - Combine two tables with the same number of columns.

Joins⚓︎

Joins

Here are the different types of the JOINs in SQL:

  • (INNER) JOIN: Returns records that have matching values in both tables
  • LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table
  • RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table
  • FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table

picture 1

Inner Join⚓︎

Inner Join
Syntax
SQL
SELECT column_name(s)
FROM table1 INNER JOIN table2
ON table1.column_name = table2.column_name;
click to see an example

CREATE TABLE Marks_family ( PersonID int, City varchar(255) ); CREATE TABLE Student_List ( StudentName varchar(255), City varchar(255) ); INSERT INTO Marks_family (PersonID, City) VALUES (123, 'Albany'); INSERT INTO Marks_family (PersonID, City) VALUES (124, 'Salem'); INSERT INTO Student_List (StudentName, City) VALUES ('Tonny', 'Albany'); INSERT INTO Student_List (StudentName, City) VALUES ('Daming', 'Salem'); INSERT INTO Student_List (StudentName, City) VALUES ('King', 'Euguene'); SELECT Marks_family.PersonID, Marks_family.City, Student_List.StudentName FROM Marks_family INNER JOIN Student_List ON Marks_family.City = Student_List.City;

Left Join⚓︎

Left Join
Syntax
SQL
SELECT column_name(s)
FROM table1 Left JOIN table2
ON table1.column_name = table2.column_name;
click to see an example

CREATE TABLE Marks_family ( PersonID int, City varchar(255) ); CREATE TABLE Student_List ( StudentName varchar(255), City varchar(255) ); INSERT INTO Marks_family (PersonID, City) VALUES (123, 'Albany'); INSERT INTO Marks_family (PersonID, City) VALUES (124, 'Salem'); INSERT INTO Student_List (StudentName, City) VALUES ('Tonny', 'Albany'); INSERT INTO Student_List (StudentName, City) VALUES ('Daming', 'Salem'); INSERT INTO Student_List (StudentName, City) VALUES ('King', 'Euguene'); SELECT Marks_family.PersonID, Marks_family.City, Student_List.StudentName FROM Student_List LEFT JOIN Marks_family ON Student_List.City = Marks_family.City;

Right Join⚓︎

Right Join
Syntax
SQL
SELECT column_name(s)
FROM table1 RIGHT JOIN table2
ON table1.column_name = table2.column_name;
click to see an example

CREATE TABLE Marks_family ( PersonID int, City varchar(255) ); CREATE TABLE Student_List ( StudentName varchar(255), City varchar(255) ); INSERT INTO Marks_family (PersonID, City) VALUES (123, 'Albany'); INSERT INTO Marks_family (PersonID, City) VALUES (124, 'Salem'); INSERT INTO Student_List (StudentName, City) VALUES ('Tonny', 'Albany'); INSERT INTO Student_List (StudentName, City) VALUES ('Daming', 'Salem'); INSERT INTO Student_List (StudentName, City) VALUES ('King', 'Euguene'); SELECT Marks_family.PersonID, Marks_family.City, Student_List.StudentName FROM Marks_family RIGHT JOIN Student_List ON Marks_family.City = Student_List.City;

Full Join⚓︎

Full Join
Syntax
SQL
SELECT column_name(s)
FROM table1 FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
(WHERE/ORDER BY);
click to see an example

CREATE TABLE Marks_family ( PersonID int, City varchar(255) ); CREATE TABLE Student_List ( StudentName varchar(255), City varchar(255) ); INSERT INTO Marks_family (PersonID, City) VALUES (123, 'Albany'); INSERT INTO Marks_family (PersonID, City) VALUES (124, 'Salem'); INSERT INTO Marks_family (PersonID, City) VALUES (127, 'Linclon'); INSERT INTO Student_List (StudentName, City) VALUES ('Tonny', 'Albany'); INSERT INTO Student_List (StudentName, City) VALUES ('Daming', 'Salem'); INSERT INTO Student_List (StudentName, City) VALUES ('King', 'Euguene'); SELECT Marks_family.PersonID, Marks_family.City, Student_List.StudentName FROM Marks_family FULL OUTER JOIN Student_List ON Marks_family.City = Student_List.City;

Self Join⚓︎

Self Join

A self join is a regular join, but the table is joined with itself.

Syntax
SQL
SELECT column_name(s)
FROM table1 T1, table2 T2
WHERE condition;
click to see an example

CREATE TABLE Marks_family ( PersonID int, City varchar(255) ); INSERT INTO Marks_family (PersonID, City) VALUES (123, 'Albany'); INSERT INTO Marks_family (PersonID, City) VALUES (124, 'Salem'); INSERT INTO Marks_family (PersonID, City) VALUES (127, 'Albany'); SELECT A.PersonID, A.City, B.PersonID FROM Marks_family A, Marks_family B WHERE A.City = B.City AND A.PersonID <> B.PersonID;

Union⚓︎

Union

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

  • Every SELECT statement 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
  • UNION selects only distinct values. Use UNION ALL to also select duplicate values!
Syntax
SQL
SELECT column_name(s) FROM table1 (WHERE)
UNION (ALL)
SELECT column_name(s) FROM table2 (WHERE)
(ORDER BY);
click to see an example

CREATE TABLE Marks_family ( PersonID int, City varchar(255) ); CREATE TABLE Student_List ( StudentName varchar(255), City varchar(255) ); INSERT INTO Marks_family (PersonID, City) VALUES (123, 'Albany'); INSERT INTO Marks_family (PersonID, City) VALUES (124, 'Salem'); INSERT INTO Marks_family (PersonID, City) VALUES (127, 'Linclon'); INSERT INTO Student_List (StudentName, City) VALUES ('Tonny', 'Albany'); INSERT INTO Student_List (StudentName, City) VALUES ('Daming', 'Salem'); INSERT INTO Student_List (StudentName, City) VALUES ('King', 'Euguene'); SELECT * FROM Marks_family UNION ALL SELECT * FROM Student_List;

References:⚓︎

Last update: April 4, 2023 08:21:07
Created: March 23, 2023 07:37:25