PostgreSql - Relationships(joins)

How to connect Two Database in innerjoin?
1.Most Important step is Create Extension.

CREATE EXTENSION dblink;

Example:

SELECT e.name, d.dept_name
FROM Employee e
JOIN dblink('dbname=department_db',
            'SELECT dept_id, dept_name FROM Department')
AS d(dept_id INT, dept_name TEXT)
ON e.department_id = d.dept_id

;

Same Database:

Example:

SELECT e.name, d.dept_name
FROM Employee e
INNER JOIN Department d
ON e.dept_id = d.dept_id;

Now we Start ,

Tables Setup

Employee table:

emp_id	name	dept_id
1	Udaya	2
2	Mathavi	3
3	Kumar	1
4	Meena	NULL

Department table:

dept_id	dept_name
1	IT
2	Finance
3	HR
4	Marketing

INNER JOIN

SELECT e.name, d.dept_name
FROM Employee e
INNER JOIN Department d ON e.dept_id = d.dept_id;

Output:

name	dept_name
Udaya	Finance
Mathavi	HR
Kumar	IT

!..Only employees with valid department.

LEFT JOIN

SELECT e.name, d.dept_name
FROM Employee e
LEFT JOIN Department d ON e.dept_id = d.dept_id;

Output:

name	dept_name
Udaya	Finance
Priya	HR
Kumar	IT
Meena	NULL

!..All employees shown, even Meena (no department → NULL).

RIGHT JOIN

SELECT e.name, d.dept_name
FROM Employee e
RIGHT JOIN Department d ON e.dept_id = d.dept_id;

Output:

name	dept_name
Udaya	Finance
Priya	HR
Kumar	IT
NULL	Marketing

!..All departments shown, even Marketing (no employee → NULL).

FULL OUTER JOIN

SELECT e.name, d.dept_name
FROM Employee e
FULL OUTER JOIN Department d ON e.dept_id = d.dept_id;

Output:

name	dept_name
Udaya	Finance
Priya	HR
Kumar	IT
Meena	NULL
NULL	Marketing

!..Combines LEFT + RIGHT → all employees + all departments.

CROSS JOIN

SELECT e.name, d.dept_name
FROM Employee e
CROSS JOIN Department d;

Output:

name	dept_name
Udaya	IT
Udaya	Finance
Udaya	HR
Udaya	Marketing
Priya	IT
Priya	Finance

... (every combination)

!..Every employee paired with every department.

Our next blog is Aggregate Function....