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 ( 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.
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.
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;
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
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.
Class Teacher Id Alpha 1 Romeo 2 Charlie 3