Author:
** 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 tablesLEFT (OUTER) JOIN
: Returns all records from the left table, and the matched records from the right tableRIGHT (OUTER) JOIN
: Returns all records from the right table, and the matched records from the left tableFULL (OUTER) JOIN
: Returns all records when there is a match in either left or right table
Inner Join⚓︎
Inner Join
Syntax
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
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
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
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.
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 withinUNION
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. UseUNION ALL
to also select duplicate values!
Syntax
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
Created: March 23, 2023 07:37:25