SQL Outer Join

In a relational DBMS, we follow the principles of normalization that allows us to minimize the large tables into small tables. By using a select statement in Joins, we can retrieve the big table back. Outer joins are of following three types.

1. Left outer join
2. Right outer join
3. Full outer join

Creating a database : Run the following command to create a database.

Create database testdb;

 Using the database : Run the following command to use a database.

use testdb;

Adding table to the database : Run the following command to add tables to a database.

CREATE TABLE Students (
   StudentID int,
   LastName varchar(255),
   FirstName varchar(255),
   Address varchar(255),
   City varchar(255)
);

Inserting rows into database :

INSERT INTO students (
StudentID,
LastName,
FirstName,
Address,
City
)
VALUES
(
111, 
'James',
 'Johnson',
 'USA',
 california
);

Output of database :

Type the following command to get output.

SELECT  * FROM students;

Types of outer join :

1.Left Outer Join : The left join operation returns all record from left table and matching records from the right table. On a matching element not found in right table, NULL is represented in that case.

Syntax :

SELECT column_name(s)
FROM table1
LEFT JOIN Table2 
ON Table1.Column_Name=table2.column_name;

2. Right Outer Join : The right join operation returns all record from right table and matching records from the left table. On a matching element not found in left table, NULL is represented in that case.

Syntax :

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

3. Full Outer Join : The full outer Join keyword returns all records when there is a match in left or right table records.

Syntax:
SELECT column_name
FROM table1
FULL OUTER JOIN table2
ON table1.columnName = table2.columnName
WHERE condition;

Example :

Creating 1st Sample table students.

CREATE TABLE students (
 id INTEGER,
 name TEXT NOT NULL,
 gender TEXT NOT NULL
);
-- insert some values
INSERT INTO students VALUES (1, 'Ryan', 'M');
INSERT INTO students VALUES (2, 'Joanna', 'F');
INSERT INTO students Values (3, 'Moana', 'F');

Creating 2nd sample table college.

CREATE TABLE college (
 id INTEGER,
 classTeacher TEXT NOT NULL,
 Strength TEXT NOT NULL
);
-- insert some values
INSERT INTO college VALUES (1, 'Alpha', '50');
INSERT INTO college VALUES (2, 'Romeo', '60');
INSERT INTO college Values (3, 'Charlie', '55');

Performing outer join on above two tables.

SELECT College.classTeacher, students.id

FROM College

FULL OUTER JOIN College ON College.id=students.id

ORDER BY College.classTeacher;

The above code will perform a full outer join on tables students and college and will return the output that matches the id of college with id of students. The output will be class Teacher from college table and id from students table. The table will be ordered by class Teacher from college table.

N

neeraj kumar 13

Follow

Improve