PostgreSQL(part-4)

Join
Used to combine data from two or more tables using common column
student table
| student_id | name | dept_id |
| ---------- | ----- | ------- |
| 101 | Kavin | 1 |
| 102 | Viyan | 2 |
| 103 | Arul | 1 |
| 104 | Meena | 3 |

department table
| dept_id | dept_name |
| ------- | --------- |
| 1 | CSE |
| 2 | ECE |
| 3 | IT |

INNER JOIN
runs only matching rows

SELECT
student.student_id,
student.name,
department.dept_name
FROM student
INNER JOIN department
ON student.dept_id = department.dept_id;

student_id	name	dept_name
101	Kavin	CSE
102	Viyan	ECE
103	Arul	CSE
104	Meena	IT

LEFT JOIN

• All rows from left table
• Matching rows from right table
• If no match,shows NULL

SELECT student.student_id,
       student.name,
       department.dept_name
FROM student 
LEFT JOIN department 
ON student.dept_id = department.dept_id;

student_id	name	dept_name
101	Kavin	CSE
102	Viyan	ECE
103	Arul	CSE
104	Meena	NULL

RIGHT JOIN

• All rows from right table
• Matching rows from left table
• If no match,shows NULL

SELECT student.student_id,
       student.name,
       department.dept_name
FROM student 
RIGHT JOIN department 
ON student.dept_id = department.dept_id;

student_id	name	dept_name
101	Kavin	CSE
103	Arul	CSE
102	Viyan	ECE
NULL	NULL	IT

FULL JOIN

• All rows from both tables
• Matches where possible
• Otherwise fills with values with NULL

SELECT student.student_id,
       student.name,
       department.dept_name
FROM student 
FULL JOIN department 
ON student.dept_id = department.dept_id;

student_id	name	dept_name
101	Kavin	CSE
103	Arul	CSE
102	Viyan	ECE
104	Meena	NULL
NULL	NULL	IT