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

BookBorrow

BookFine

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

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

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

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

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

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

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

Example

Query

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

Output

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

admin