Implicit Join vs Explicit Join in SQL

JOIN clause is used to combine rows from two or more tables, based on a relation between them. There are two different syntax forms to perform JOIN operation:

• Explicit join
• Implicit join

Step 1: Creating the Database

Use the below SQL statement to create a database called geeks:

CREATE DATABASE geeks;

Step 2: Using the Database

Use the below SQL statement to switch the database context to geeks:

USE geeks;

Step 3: Creating the Tables

Use the below SQL statement to create a table called student:

CREATE TABLE student(
  ROLL_NO INT PRIMARY KEY,
  NAME VARCHAR(25),
  AGE INT);

Use the below SQL statement to create a table called course:

CREATE TABLE course(
  COURSE_ID INT,
  ROLL_NO INT);

Step 4: Adding Data to the Tables

Use the below SQL statement to add the data to the student table:

INSERT INTO student 
VALUES 
(1, 'Anjali', 20), 
(2, 'Rachna', 18), 
(3, 'Shubham', 21), 
(4, 'Harsh', 25), 
(5, 'Shivam', 18), 
(6, 'Harshit', 20);

Use the below SQL statement to add the data to the course table:

INSERT INTO course 
VALUES 
(1, 1),
(2, 2),
(2, 3),
(5, 4),
(3, 5);

Step 5: Student Table

Use the below SQL statement to view the content of the student table:

SELECT * FROM student;

Course Table:

Use the below SQL statement to view the content of the course table:

SELECT * FROM course;

Step 6: Explicit Join 

This notation uses the ON keyword to specify the predicates for Join and the JOIN keyword to specify the tables to join. Join can be of any type i.e. INNER JOIN, LEFT JOIN, RIGHT JOIN, or FULL JOIN. It is easier to understand and less prone to errors.

Syntax:

SELECT column_names
FROM table1
JOIN table2
ON table1.column_name = table2.column_name

The following query will show the course id, names, and age of students enrolled in different courses by using explicit join notation.

Query:

SELECT COURSE_ID, NAME, AGE 
FROM student
JOIN course
ON student.ROLL_NO = course.ROLL_NO;

Output:

Step 7: Implicit Join 

This notation simply lists the tables for joining (in the FROM clause of the SELECT statement), using commas to separate them and WHERE clause to apply to join predicates. It performs a CROSS JOIN. It is difficult to understand and more prone to errors.

Syntax:

SELECT column_names
FROM table1, table2
WHERE table1.column_name = table2.column_name

The following query will show the course id, names, and age of students enrolled in different courses by using implicit join notation.

Query:

SELECT COURSE_ID, NAME, AGE 
FROM student, course
WHERE student.ROLL_NO = course.ROLL_NO;

Output:

My Personal Notes arrow_drop_up

Save