SQL Join – Inner and Outer Joins

Individual tables in a database are not enough. The most common need of database is to access data from multiple tables. SQL Join is the way to fulfill the need of joining two or more tables to get required data in an application.

SQL Join is actually a select query that fetches records from more than one table or views. You can include any columns from the tables which are joined. If the name of the columns is same in the tables then you must differentiate them by qualifying column names with table name or table alias. It is essential to avoid ambiguity of table column names.

Join condition can be given either in the WHERE clause or the FROM clause of the query. The basis of an SQL join is the condition specified. The specified join condition checks the value of column in one table with value in the second table. Query returns the rows where the values in column of two tables satisfy the condition.

We will be using these tables for the examples of SQL JOIN in the article. The DDL and DML for these tables are given at the end

BookMas

BOOKID BOOKNAME BOOKPRICE
ACC121 Accounting 120
COMP111 Artificial Intelligence 130
BOM1121 Business Model 145
COMP126 Computer Graphics 1450
COMP116 Python basics 145
COMP125 Data Structures 200
COMP102 Linear Structures 100
COMP121 Machine Learning 1200

BookBorrow

STUID BOOKID ISSUEDATE EXPRETDATE ACTRETDATE STATUS
1022 COMP125 11-FEB-19 11-MAR-19 10-MAR-19 1
1020 COMP102 09-FEB-19 09-MAR-19 15-MAR-19 1
1042 ACC121 04-JUN-19 04-JUL-19 10-JUL-19 1
1242 BOM1121 14-AUG-19 14-SEP-19 10-SEP-19 1
1020 COMP111 09-FEB-19 09-MAR-19 15-MAR-19 1
1021 COMP121 11-DEC-19 11-DEC-19 10-DEC-19 1

BookFine

STUID FINEDATE FINEAMT
1020 09-JUL-19 4
1142 09-JUL-19 23
1022 09-DEC-19 34

SQL Join- Inner

SQL Inner Join is also called Equijoin. It is called so because it returns those rows where the rows satisfy the joining condition. It is defined with an equality operator so the name equijoin.

Syntax of Inner or Equi Join

SELECT Column-Name1, Column-Name2, Column-Name3,….

FROM table1

INNER JOIN Table2 ON table1.Column-Name=Table2.Column-Name

OR

SELECT Column-Name1, Column-Name2, Column-Name3,….

FROM table1, table2

WHERE  table1.Column-Name=Table2.Column-Name

Example

Select the book ID, Book Name, Issue Date and Book Price for all the books borrowed by members. In this query we will join BookBorrow and BookMas table on column BooKID

Query

Select B.BOOKID, BOOKNAME, ISSUEDATE, BOOKPRICE from BOOKMAS M INNER JOIN BOOKBORROW B on B.BOOKID=M.BOOKID


Output

BOOKID BOOKNAME ISSUEDATE BOOKPRICE
ACC121 Accounting 04-JUN-19 120
COMP111 Artificial Intelligence 09-FEB-19 130
BOM1121 Business Model 14-AUG-19 145
COMP125 Data Structures 11-FEB-19 200
COMP102 Linear Structures 09-FEB-19 100
COMP121 Machine Learning 11-DEC-19 1200

Example

Select the Student ID, Book ID and Fine Amount dues against a stundent.

Query

Select B.STUID, BOOKID, FINEAMT from BOOKBORROW B INNER JOIN BOOKFINE F on B.STUID=F.STUID

Output

STUID BOOKID FINEAMT
1022 COMP125 34
1020 COMP102 4
1020 COMP111 4

SQL Join- Outer

An outer Join returns the additional data from tables in addition to those satisfying the join condition. Depending upon from where the additional rows are returned the join can be a Left, Right or Full join.

Syntax

SELECT Column-Name1, Column-Name2, Column-Name3,….

FROM table1

[LEFT|RIGHT|FULL] OUTER JOIN Table2 ON table1.Column-Name=Table2.Column-Name

OR

Syntax Left Outer with WHERE Clause

SELECT Column-Name1, Column-Name2, Column-Name3,….

FROM table1, table2

WHERE  table1.Column-Name (+)=Table2.Column-Name

Syntax Right Outer with WHERE Clause

SELECT Column-Name1, Column-Name2, Column-Name3,….

FROM table1, table2

WHERE  table1.Column-Name=Table2.Column-Name(+)

SQL Join-Left Outer

In a Left outer Join the SELECT statement returns all the rows from the left table and only those rows from right table which match the Join condition. The unmatched rows contain null in the unmatched columns

Example

Return all details from BOOKMAS but only those from BOOKBORROW that match BOOKID in BOOKMAS

Query

Select M.BOOKID, BOOKNAME, ISSUEDATE, BOOKPRICE from BOOKMAS M LEFT OUTER JOIN BOOKBORROW B on B.BOOKID=M.BOOKIDOutput

BOOKID BOOKNAME ISSUEDATE BOOKPRICE
COMP102 Linear Structures 09-FEB-19 100
COMP111 Artificial Intelligence 09-FEB-19 130
COMP121 Machine Learning 11-DEC-19 1200
COMP125 Data Structures 11-FEB-19 200
ACC121 Accounting 04-JUN-19 120
BOM1121 Business Model 14-AUG-19 145
COMP126 Computer Graphics 1450
COMP116 Python basics 145

Example

Display  BOOKID  and FINEAMT from BOOKBORROW but only those from BOOKFINE which match the STUID of BOOKBORROW

Query

Select B.STUID, BOOKID, FINEAMT from BOOKBORROW B LEFT OUTER JOIN BOOKFINE F on B.STUID=F.STUID


Output

STUID BOOKID FINEAMT
1022 COMP125 34
1020 COMP102 4
1020 COMP111 4
1242 BOM1121
1021 COMP121
1042 ACC121

SQL Join-Right Outer

In a Right outer Join the SELECT statement returns all the rows from the Right table and only those rows from Left table which satisfy the Join condition. The unmatched rows contain null in the unmatched columns.

Example

Display all details from BOOKBORROW but only those from BOOKMAS that match BOOKID in BOOKBORROW

Query

Select B.BOOKID, BOOKNAME, ISSUEDATE, BOOKPRICE from BOOKMAS M RIGHT OUTER JOIN BOOKBORROW B on B.BOOKID=M.BOOKID

Output

BOOKID BOOKNAME ISSUEDATE BOOKPRICE
COMP125 Data Structures 11-FEB-19 200
COMP102 Linear Structures 09-FEB-19 100
COMP111 Artificial Intelligence 09-FEB-19 130
COMP121 Machine Learning 11-DEC-19 1200
ACC121 Accounting 04-JUN-19 120
BOM1121 Business Model 14-AUG-19 145

Example

Display  BOOKID  and FINEAMT from BOOKFINE but only those rows from BOOKBORROW which match the STUID of BOOKFINE

Query

Select B.STUID, BOOKID, FINEAMT from BOOKBORROW B RIGHT OUTER JOIN BOOKFINE F on B.STUID=F.STUID

Output

STUID BOOKID FINEAMT
1020 COMP102 4
1020 COMP111 4
1022 COMP125 34
23

SQL Join-Full Outer

In a Full Outer SQL Join the SELECT statement returns rows that satisfy the join condition plus the rows from the Right table and Left table that do not satisfy the join condition. The unmatched rows contain null in the unmatched columns.

Example

Query

Select B.BOOKID, BOOKNAME, ISSUEDATE, BOOKPRICE from BOOKMAS M FULL OUTER JOIN BOOKBORROW B on B.BOOKID=M.BOOKID

Output

BOOKID BOOKNAME ISSUEDATE BOOKPRICE
COMP125 Data Structures 11-FEB-19 200
COMP102 Linear Structures 09-FEB-19 100
COMP111 Artificial Intelligence 09-FEB-19 130
COMP121 Machine Learning 11-DEC-19 1200
ACC121 Accounting 04-JUN-19 120
BOM1121 Business Model 14-AUG-19 145
Python basics 145
Computer Graphics 1450

Example

Query

Select B.STUID, BOOKID, FINEAMT from BOOKBORROW B FULL OUTER JOIN BOOKFINE F on B.STUID=F.STUID

Output

STUID BOOKID FINEAMT
1020 COMP102 4
1020 COMP111 4
1021 COMP121
1022 COMP125 34
1042 ACC121
1242 BOM1121
23

DDL & DML of Example Database

DB

Create table BookBorrow 
( 
  StuID number(6), 
  BookID varchar2(20), 
  IssueDate date, 
  ExpRetDate date, 
  ActRetDate date, 
  Status char(1) 
);

Create table BookFIne 
( 
  StuID number(6), 
  FineDate date, 
  FineAmt number(7,2) 
);

INSERT INTO BOOKBORROW VALUES(1020,'COMP102','09-FEB-2019','09-MAR-2019','15-MAR-2019','1');

INSERT INTO BOOKBORROW VALUES(1020,'COMP111','09-FEB-2019','09-MAR-2019','15-MAR-2019','1');

INSERT INTO BOOKBORROW VALUES(1021,'COMP121','11-DEC-2019','11-DEC-2019','10-DEC-2019','1');

INSERT INTO BOOKBORROW VALUES(1022,'COMP125','11-FEB-2019','11-MAR-2019','10-MAR-2019','1');

select * from BookBorrow 
;

INSERT INTO BOOKBORROW VALUES(1042,'ACC121','04-JUN-2019','04-JUL-2019','10-JUL-2019','1') 
;

INSERT INTO BOOKBORROW VALUES(1242,'BOM1121','14-AUG-2019','14-SEP-2019','10-SEP-2019','1') 
;

select * from BookBorrow 
;

select bookID 
from BookBorrow 
;

CREATE TABLE BOOKMAS 
(BookID varchar2(40), 
BOOKName Varchar2(50), 
BOOKPRICE number(7,2) 
);

INSERT INTO BOOKMAS values('COMP125','Data Structures',200);

INSERT INTO BOOKMAS values('COMP102','Linear Structures',100);

INSERT INTO BOOKMAS values('COMP111','Artificial Intelligence',130);

INSERT INTO BOOKMAS values('COMP121','Machine Learning',1200);

INSERT INTO BOOKMAS values('ACC121','Accounting ',120);

INSERT INTO BOOKMAS values('BOM1121','Business Model ',145);

INSERT INTO BOOKMAS values('COMP116','Python basics ',145);

INSERT INTO BOOKMAS values('COMP126','Computer Graphics ',1450);

select * from BookMAS;

select * from BookFINE;

select * from BOOKBORROW;

select STUID from BOOKBORROW;

INSERT into Bookfine values(1022,'09-DEC-2019' 34);

INSERT into Bookfine values(1022,'09-DEC-2019', 34);

INSERT into Bookfine values(1020,'09-JUL-2019', 4);

INSERT into Bookfine values(1142,'09-JUL-2019', 23);

Read more about the topic from Here

Be First to Comment

    Leave a Reply

    Your email address will not be published. Required fields are marked *