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.
- Left outer join
- Right outer join
- 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.
Adding table to the database : Run the following command to add tables to a database.
CREATE TABLE Students (
Inserting rows into database :
INSERT INTO students (
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.
LEFT JOIN Table2
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.
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.
FULL OUTER JOIN table2
ON table1.columnName = table2.columnName
Creating 1st Sample table students.
CREATE TABLE students (
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 (
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
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.
Unlock the Power of Placement Preparation!
Feeling lost in OS, DBMS, CN, SQL, and DSA chaos? Our Complete Interview Preparation
Course is the ultimate guide to conquer placements. Trusted by over 100,000+ geeks, this course is your roadmap to interview triumph.
Ready to dive in? Explore our Free Demo Content and join our Complete Interview Preparation