Postgresql SQL Joins – Inner | Left | Right

TABLE DETAILS

Tables used for explaining Postgresql joins are : Courses, Subjects and Students

-- CREATING COURSES TABLE

create table courses (
	course_id integer,
	course_name varchar
);

-- INSERTING RECORDS INTO COURSES TABLE

insert into courses (course_id, course_name) 
values 
(1, 'MCA'),
(2, 'BCA'),
(3, 'BE'),
(4, 'ME'),
(5, 'BCOM'),
(6, 'MCOM');

-- CHECKING WHETHER RECORDS ARE INSERTED INTO THE COURSES TABLE

select * from courses;

-- CREATING SUBJECTS TABLE

create table subjects (
	subject_id integer,
	subject_name varchar,
	course_name varchar
);

-- INSERTING RECORDS INTO SUBJECTS TABLE

insert into subjects (subject_id, subject_name, course_name)
values
(1, 'Computer Programming with C.', 'MCA'),
(2, 'Discrete Mathematical Structure.', 'MCA'),
(3, 'Database Management System.', 'MCA'),
(4, 'Unix and Shell Programming.', 'MCA'),
(5, 'Software Engineering & TQM.', 'MCA'),
(6, 'Operation Research & Optimisation Techniques.', 'MCA'),
(7, 'Object Oriented Programming with Java.', 'MCA'),
(8, 'Fundamentals of IT & Computers', 'MCA'),
(9, 'Fundamentals of Statistics', 'BCA'),
(10, 'Digital Electronics', 'BCA'),
(11, 'Basic Mathematics', 'BCA'),
(12, 'C Language Lab', 'BCA'),
(13, 'Building Technology.', 'BE'),
(14, 'Structural Analysis.', 'BE'),
(15, 'Engineering Thermodynamics.', 'BE'),
(16, 'Electrical Drives and Controls.', 'BE'),
(17, 'Data Structures and Applications.', 'BE'),
(18, 'Analog and Digital Electronics.', 'BE'),
(19, 'Computer Organisation.', 'BE'),
(20, 'Advanced Machine Design.', 'ME'),
(21, 'Measurement Techniques & Data Analysis.', 'ME'),
(22, 'Internal Combustion Engine.', 'ME'),
(23, 'Analysis & Synthesis of Mechanisms.', 'ME'),
(24, 'Computer-Aided Manufacturing.', 'ME'),
(25, 'Theory of Elasticity & Plasticity.', 'ME');

-- CHECKING WHETHER RECORDS ARE INSERTED INTO TABLE

select * from subjects;

-- CREATING STUDENTS TABLE

create table students (
	student_id integer,
	student_name varchar,
	student_mobile_number varchar,
	course_name varchar
);

-- INSERTING RECORDS INTO STUDENTS TABLE

insert into students (student_id, student_name, student_mobile_number, course_name)
values
(1, 'Rohit Naidu', '9876543210', 'MCA'),
(2, 'Digambar Shahapure', '8765432109', 'MCA'),
(3, 'Rishikesh Ballurkar', '7654321098', 'BCA'),
(4, 'Nikhil Dhobale', '6543210987', 'BCA'),
(5, 'Ketan Dhobale', '9012345678', 'BE'),
(6, 'Rohit Aher', '8901234567', 'ME');

-- CHECKING WHETHER RECORDS ARE INSERTED INTO STUDENTS TABLE

select * from students;

JOIN QUERIES?

By providing a table name to a select query, you can access one table at a time. “select * from <<tablename (i.e courses)>>;” as an example. What if you need to extract information from several tables? Select queries can query multiple tables at the same time to produce numerous rows.

Join queries are select queries that can access multiple tables at the same time. Based on a set of conditions, join queries combine rows from the primary table with rows from the secondary table or tables. In join queries, we use the ON clause to evaluate conditions, that takes a Boolean value expression similar to the WHERE clause.

JOIN TYPES

Inner Join
Left Outer Join / Left Join
Right Outer Join / Right Join
Full Outer Join / Full Join

INNER JOIN

INNER JOIN – The joined table has a row for each row in Table 2 which satisfies given criteria for each record in Table 1.
Syntax: select * from table1 inner join table2 on table1.id = table2.id;
Syntax Explanation:
Select <<select is a keyword>>
* << * means select all columns or you can also provide specific column names to use from both the tables like table1.column_name, table2.column_name >>
from table1 << from is a keyword and table1 means primary table name >>
inner join << specifies join type to use >>
table2 << table2 is the secondary table name >>
ON << on is a condition evaluation keyword >>
table1.id = table2.id; << This is the condition criteria>>

-- INNER JOIN Example 1 : Selecting all the columns by joining two or multiple table.
select * from subjects inner join courses on 
subjects.course_name = courses.course_name;

-- INNER JOIN Example 2 : Selecting particular columns by using inner join on two or multiple table.
select subjects.subject_id, subjects.subject_name, courses.course_name, courses.course_name from subjects inner join courses on 
subjects.course_name = courses.course_name;

-- INNER JOIN Example 3 - Selecting data from all columns using INNER JOIN by joining 3(three) tables.
select * from subjects 
inner join courses on  subjects.course_name = courses.course_name
inner join students on students.course_name = courses.course_name;

Selecting data from all columns using INNER JOIN by joining 3(three) tables.

LEFT JOIN / LEFT OUTER JOIN

Left Outer Join / Left Join:
An inner join is performed first internally when performing a Left Outer Join or a Left Join. The joined table contains all the rows that satisfy the given condition, as well as all of the extra rows from the primary table that do not satisfy the given condition and have null values in the other columns chosen from the secondary table.
Syntax: select * from table1(Left Table) Left Join table2 on table1.id = table2.id;
Syntax Explanation:
Select <<select is a keyword>>
* <<* means select all columns or you can also provide specific column names to use from both the tables like table1.column_name, table2.column_name>>
from table1 <<from is a keyword and table1 means primary table name>>
left join <<specifies join type to use>>
table2 <<table2 is the secondary table name>>
ON <<on is a condition evaluation keyword>>
table1.id = table2.id; <<This is the condition criteria>>

-- LEFT JOIN Example 1: Selecting all the columns by using left join on two or multiple tables, here left table is subjects.
select * from subjects left join courses on 
subjects.course_name = courses.course_name;

Fetching all columns and records using left join on two or multiple tables.

-- LEFT JOIN Example 2: Selecting all the columns by using left join on two or multiple tables, here left table is courses.
select * from courses left join subjects on  
subjects.course_name = courses.course_name;

-- LEFT JOIN Example 3: Selecting specific columns by joining two or multiple table, here left table is courses

select subjects.subject_id, subjects.subject_name, courses.course_name, courses.course_name from courses left join subjects  on subjects.course_name = courses.course_name;

Selecting particular columns from both the tables using inner join.

RIGHT JOIN / RIGHT OUTER JOIN

Right Outer Join / Right Join:
An inner join is performed first internally when performing a Right Outer Join or a Right Join. The joined table contains all the rows that satisfy the given condition, as well as all of the extra rows from the secondary table that do not satisfy the given condition and have null values in the other columns chosen from the primary table.
Syntax: select * from table1 Left Join table2(Right Table) on table1.id = table2.id;
Syntax Explanation:
Select <<select is a keyword>>
* <<* means select all columns or you can also provide specific column names to use from both the tables like table1.column_name, table2.column_name>>
from table1 <<from is a keyword and table1 means primary table name>>
right join <<specifies join type to use>>
table2 <<table2 is the secondary table name>>
ON <<on is a condition evaluation keyword>>
table1.id = table2.id; <<This is the condition criteria>>

-- RIGHT JOIN / RIGHT OUTER JOIN  Example 1: Selecting all the columns by joining two or multiple table, here right table is courses.
select * from subjects right join courses  on 
subjects.course_name = courses.course_name;

Selecting all columns from the table using Right Join.

-- RIGHT JOIN / RIGHT OUTER JOIN Example 2: Selecting all the columns by joining two or multiple table, here right table is subjects.
select * from courses right join subjects on 
subjects.course_name = courses.course_name;

-- RIGHT JOIN/RIGHT OUTER JOIN Example 3: Selecting specific columns by joining two or multiple table, here right table is courses.
select subjects.subject_id, subjects.subject_name, courses.course_name, courses.course_name from subjects right join courses on subjects.course_name = courses.course_name;

Selecting specific column by joining two or multiple tables using Right Join

FULL JOIN / FULL OUTER JOIN

Full Outer Join / Full Join:

An inner join is performed first internally when performing a Full Outer Join or a Full Join. Null values are added in selected columns from table 2 for all rows in table 1 where the condition is not met. For all rows of table 2 where the condition is not met, null values are added to the columns of table 1.
Syntax: select * from table1(Left) full Join table2(Right Table) on table1.id = table2.id;
Syntax Explanation:
Select <<select is a keyword>>
* <<* means select all columns or you can also provide specific column names to use from both the tables like table1.column_name, table2.column_name>>
from table1 <<from is a keyword and table1 means primary table name>>
full join <<specifies join type to use>>
table2 <<table2 is the secondary table name>>
ON <<on is a condition evaluation keyword>>
table1.id = table2.id; <<This is the condition criteria>>

-- FULL JOIN/FULL OUTER JOIN Example 1: Selecting all the columns by joining two or multiple table.
select * from subjects full join courses  on 
subjects.course_name = courses.course_name;