Ohio Computer Academy - IT Training & Vocational Education in Dublin, OH
Ohio Computer Academy - IT Training & Vocational Education in Dublin, OH
All Courses
  • Free Training Courses
  • AI for Your Role
  • AI & Machine Learning
  • Cloud Tech
  • Data Science
  • DevOps
  • Dashboarding/BI
  • Database
  • WebDevelopment
  • Programming & Coding
  • MS Office & Workplace Productivity
  • QA & Test Automation
  • Professional & Career Development
  • Cyber Security

Courses

  • Computer Fundamentals for Beginners
  • MS Word for Beginners
  • Microsoft Excel for Beginners
  • Microsoft Azure for Beginners
  • AWS Cloud for Beginners
  • Oracle SQL Fundamentals
  • Tableau Fundamentals
  • Power BI Fundamentals
Explore All Programs

Courses

  • AI Essentials
  • AI for Administrative Professionals New Launch
  • AI for Managers & Team Leads
  • AI for Data Analytics & Power BI
  • AI for Software Developers
  • AI for Database & SQL Professionals
  • AI for Project Managers
  • AI for Marketing Professionals
  • AI for Sales Professionals
  • AI for HR Professionals
  • AI for Finance Professionals
  • AI for Customer Support Professionals
  • AI for QA & Test Engineers
  • AI for IT Support & System Admins
  • AI for Cybersecurity Analysts
  • AI for Job Seekers
Explore All Programs

Courses

  • Learn Claude Basics New Launch
  • Learn Claude Advanced New Launch
  • Applied Artificial Intelligence (AI) New Launch
  • AI for Workplace Productivity
  • Azure Fundamentals with AI
  • Advanced Microsoft Azure with AI
  • AI Implications for Business Strategy
  • Mastering ChatGPT & Generative AI Tools
  • Prompt Engineering
  • Agentic AI Solutions
  • AI Product Management
  • AI for Quality Control
Explore All Programs

Courses

  • AZ-900 Azure Fundamentals
  • DP-900 Azure Data Fundamentals
  • DP-100 Data Science on Azure
  • AWS Essentials
  • AWS Cloud Practitioner
  • AWS Solutions Architect
Explore All Programs

Courses

  • DP-100 Data Science on Azure
Explore All Programs

Courses

  • DevOps Essentials
  • DevOps with AWS
  • DevOps with Azure
Explore All Programs

Courses

  • Business Analytics: From Data to Insights New Launch
  • Power BI Advanced
  • Data Analytics with Excel
  • Tableau Beginner
  • Tableau Data Consumer
  • Alteryx & Tableau
  • Tableau Advanced
  • Tableau Certification prep
  • Power BI Beginner
  • Cognos Analytics
Explore All Programs

Courses

  • SQL Server for Beginners
  • SQL for Analysts
  • SQL Server Advanced
  • SQL Server T-SQL Programming
  • SSIS
  • Oracle SQL for Beginners
  • Oracle PL/SQL
  • Oracle SQL Advanced
  • Big Data with Hadoop: HDFS, MapReduce & Ecosystem Tools
  • MS Access
Explore All Programs

Courses

  • Angular Frontend Development
  • Spring 5 Framework
  • Full Stack Web Development
Explore All Programs

Courses

  • Learn Python
  • Python PCEP
  • Python PCAP
  • JavaScript & jQuery
  • Java Certification
  • Python for Data Science
  • Machine Learning with Python
Explore All Programs

Courses

  • Analyzing Data with Excel New Launch
  • Workplace Digital Skills
  • Excel Beginners to Advanced Trending Now
  • Excel for Finance
  • Excel for HR
  • Workplace Computer Skills
Explore All Programs

Courses

  • Selenium QA Automation (Java)
  • Selenium QA Automation (Python)
  • API Testing
  • QA Automation with Python (Advanced)
Explore All Programs

Courses

  • Scrum Master Certification
  • Lean Six Sigma Green Belt Certification
  • Business Analyst
Explore All Programs

Courses

  • Cyber Security Foundations for Beginners
  • Computer Networking for Cybersecurity
  • Windows & Linux Security Administration
  • Ethical Hacking & Penetration Testing
  • Artificial Intelligence (AI) in Cybersecurity
Explore All Programs
  • About Us
    • OCA Skill Sprint Method
    • Meet the Founder
  • Blogs
    • Career Building
    • AI / Machine Learning
    • MS Excel
    • Programming Essentials
    • Database
    • Cloud Computing
    • Tableau
    • Power BI
    • Web Development
    • Tips for Success
    • Qlik
    • Soft Skills
    • Technology
    • Business Intelligence Analyst
    • Message from the CEO
    • DATA ANALYST
  • TechCred
  • Find Your Training
  • Contact Us
Ohio Computer Academy - IT Training & Vocational Education in Dublin, OH
Ohio Computer Academy - IT Training & Vocational Education in Dublin, OH

 

  • info@ohiocomputeracademy.com
  • +1 866-648-7284
  • Home
  • About Us
  • Blogs
    • Career Building
    • AI / Machine Learning
    • MS Excel
    • Programming Essentials
    • Database
    • Cloud Computing
    • Tableau
    • Power BI
    • Web Development
    • Tips for Success
    • Qlik
    • Soft Skills
    • Technology
    • Business Intelligence Analyst
    • Message from the CEO
    • DATA ANALYST
  • TechCred
  • Find Your Training
  • Contact Us
Find With Us
  • Home
  • Blogs
  • Database
  • Joins in SQL

Joins in SQL

Joins allow us to combine data from two or more tables based on  Primary and Foreign keys. In this article, we will learn how Joins in SQL are implemented. 

Though join concepts are the same in all databases, here we will learn about joins in Oracle. If you are new to Oracle, you can install Oracle and try these joins.

Tables used in this tutorial:

DeptNew: contains details of the department in an organization. It has 6 rows.

EmpNew: table contains the Employee details. Each employee has a JobID and a Manager. Employees works in various departments. Two of the employees Sam and Raj are not assigned to any department.

SalaryNew: This table provide details of the various Jobs and Salaries assigned to them.

Watch Our YouTube Video on Joins

Joins in SQL

The structure of the tables : 

Types of Joins in SQL :

There are mainly four types of Joins in SQL:

  • Equi join/Inner Join

  • Outer join

  • Cartesian join

  • Self join

Equi join/Inner Join

When two tables are joined together using the equality of values in one or more columns, they make an equi join. The equi join or Inner join will display the matching rows in two tables.

Consider the before mentioned tables, EmpNew and DeptNew to answer the exercise below,

Exercise1. List the Employees – EmpNo, Ename, Deptno and DName.

Why do we need a join for this exercise:  

EmpNo, Ename and Deptno are in EmpNew table but department name (DName) is in the DeptNew table. To get the department name, we need to join the two tables.

Query:

Select EmpNo, Ename, e.Deptno, Dname
From EmpNew e, DeptNew d /* e and d are table alias /
Where e.DeptNo = d.DeptNo; / Join Condition */

Note: e.Deptno is qualifying deptno with the table name because Deptno column is present in both the tables. If Deptno is not qualified, oracle cannot decide from which table to pick Deptno and will give column ambiguously defined error.

Output:

This join query only displayed 5 rows, because equi join displays the matching/equal rows from both the tables and in this case only 5 in Deptno between the two tables match.

Multiple tables can be joined using the AND operator.

Below query will use Deptnew, EmpNew and SalaryNew table to display Employees, and their Departments and Salary Information.

Select Empno, Ename, e.Deptno, Dname, Salary, JobTitle
From EmpNew e, DeptNew d, SalaryNew s /* 3 tables are joined /
Where e.Deptno = d.deptno
AND e.JobID = s.JobID
Order By Empno; /
Order By clause is used to sort the output by Empno */

Output:

 

Outer Join

If there are any values in one table that do not have corresponding values in the other, in an equi join, that row will not be selected. Such rows can be forcefully selected by using the outer join symbol (+). The corresponding columns for that row will have Nulls.

Exercise2. Display the employees working in each department. Display the records even if no employees belong to that department.

Select Empno, Ename, e.Deptno, d.Deptno AS “Deptno from DeptNew”, Dname
From EmpNew e, DeptNew d
Where e.Deptno(+) = d.deptno;

Output:

In the above example, we have displayed the Deptno from both EmpNew and DeptNew tables.

The output of the above SQL shows that in two departments (Deptno 800 and 400) there are no employees.
Outer join displays the matching rows and also the rows which are not matching, as in this example.
This is also called Right Outer join i.e. all matching rows and all the rows from the right table will be displayed. Any non-matching rows in the left will be displayed as NULLs.

If the plus sign (+) is placed on the other side, then the employee details with no corresponding department name will be displayed with NULL values in Department columns.

Query:

Select   Empno, Ename, e.Deptno, d.Deptno “Deptno from DeptNew”, Dname
From   EmpNew e, DeptNew d
Where e.Deptno = d.deptno(+);

This is also called left join, it displays the matching rows from the two tables and also all the rows present in the left table. The non-matching rows in the right table will be displayed as null.

Cartesian/Cross Join

When no WHERE clause is specified, each row of one table joins every row of the other table. This results in a Cartesian product. For e.g. If Table A has 10 rows and Table B has 10 rows, Cartesian join will output 100 rows.

Cartesian joins should be avoided as they are resource intensive.

Exercise3. Display the records from EmpNew and SalaryNew table using the Cartesian join.

Query:

Select Empno, Ename, JobTitle
From EmpNew e, SalaryNew s ;

Output:

The EmpNew table has 7 rows, SalaryNew table has 4 rows.

The above query output will display 7 * 4 = 28 rows.

Self Join

A table can join to itself too i.e. each row in a table is combined with itself and with every other row of the table. The self join can be viewed as a join of two copies of the same table. The table is not actually copied, but SQL performs the command as though it were.

In the EmpNew table, every employee has a manager, except for Empno 1  – John as he is the CEO. He is the manager of Empno 2, 4 and 7. Observe that Manager is also an employee for e.g. Empno 2 – Jim is a MGR of Empno 3 – Doug.  Similarly, EmpNo 4 – Karen is a manager of Empno 6 – Sam.

Exercise4. Display the Employee name and his manager details.

Query:

Select Worker.Ename, Worker.Empno, Manager.Empno Manager_ID, Manager.Ename Manager_Name
From EmpNew Worker, EmpNew Manager /* copies of the same table /
Where Worker.Mgr = Manager.Empno; /
table joining to itself */

Output:

Like
Chandraish Sinha

Chandraish Sinha is the Founder and President of Ohio Computer Academy, a leading institution committed to delivering high-quality IT education. With a passion for teaching and a belief in his company’s mission—Inspire, Educate & Evolve—Chandraish brings over 25 years of experience in the Information Technology industry.

Explore more of his work on his Amazon Author Profile.

0 Comments

Add Your Comment

Post a Comment

Required fields are marked *

✅ Thanks for adding your comment! It will appear after moderation.

0 Comments

Related Blogs
View All Blogs
How to install Oracle 11g and unlock sample databases
Read More
What are the 12 Codd rules of RDBMS
Read More
Basics of RDBMS
Read More
Ohio Computer Academy - IT Training & Vocational Education in Dublin, OH
Ohio Computer Academy - IT Training & Vocational Education in Dublin, OH

Registered with the Ohio State Board of Career Colleges and Schools — School Registration No. 2395

About Us
  • Contact Us
  • Our Story
  • Training Gallery
  • Reviews
  • Books
  • Greater Columbus Training Center
Trainings/Services
  • Course Categories
  • Learning Paths
  • Frequently Asked Questions
  • AI Advantage
Contact Us
  • Phone:+1 866-648-7284
  • E-mail: info@ohiocomputeracademy.com
  • Address:

    Dublin, Ohio
    5900 Sawmill Road, Suite # 150, Dublin, OHIO 43017 USA

Thank you
Failed
Free Training Courses
  • Computer Fundamentals for Beginners
  • MS Word for Beginners
  • Microsoft Excel for Beginners
AI Courses
  • Claude Basics
  • Claude Advanced
  • AI for Workplace Productivity
  • Prompt Engineering
  • Agentic AI Solutions
  • AI for Quality Control
Cloud Tech
  • DP-900 Azure Data Fundamentals
  • DP-100 Data Science on Azure
  • AWS Technical Essentials
  • AWS Cloud Practitioner
  • AWS Solutions Architect
DevOps
  • DevOps Essentials
  • DevOps with AWS
  • DevOps with Azure
Dashboarding/BI
  • Tableau Beginner
  • Power BI Beginner
  • Data Analytics with Excel
Database
  • Oracle SQL for Beginners
  • SQL for Analysts
  • SQL Server for Beginners
MS Office & Workplace Productivity
  • Workplace Digital Skills
  • Excel for Finance
  • Excel for HR
  • Excel Beginners to Advanced
  • Workplace Computer Skills
Learning Hub
  • Begin Your IT Career
  • Generative AI & Workplace Productivity Foundations
  • Claude AI Learning Path
  • Data Visualization Foundations
  • Data Analytics Career Path
  • AI for Business & Data Applications
  • Programming Foundations & Development Path

© 2026 Ohio Computer Academy. All rights reserved.

  • Privacy
  • Terms & Conditions