Tutorialsteacher

Follow Us

Articles
  • C#
  • C# OOP
  • ASP.NET Core
  • ASP.NET MVC
  • LINQ
  • Inversion of Control (IoC)
  • Web API
  • JavaScript
  • TypeScript
  • jQuery
  • Angular 11
  • Node.js
  • D3.js
  • Sass
  • Python
  • Go lang
  • HTTPS (SSL)
  • Regex
  • SQL
  • SQL Server
  • PostgreSQL
  • MongoDB
  • SQL - Getting Started
  • What is SQL
  • Create Table
  • ALTER TABLE Statements
  • Rename Columns
  • Modify Column Type
  • Drop Columns
  • Rename Tables
  • Drop Tables
  • Insert Statement
  • Update Statement
  • Delete Statement
  • Truncate Statement
  • Merge Statement
  • Null Value
  • Select Query
  • WHERE Clause
  • GROUP BY Clause
  • HAVING Clause
  • ORDER BY Clause
  • SQL - Inner Join
  • SQL - Left Join
  • SQL - Right Join
  • SQL - Full Join
  • SQL - BETWEEN
  • SQL - IN
  • SQL - LIKE
  • SQL - INTERSECT
  • SQL - MINUS
  • SQL - UNION
  • SQL - UNION ALL
  • SQL - DISTINCT
  • SQL - ANY, SOME
  • SQL - ALL
  • SQL - AVG()
  • SQL - COUNT()
  • SQL - MAX()
  • SQL - MIN()
  • SQL - SUM()
Entity Framework Extensions - Boost EF Core 9
  Bulk Insert
  Bulk Delete
  Bulk Update
  Bulk Merge

SQL - INNER JOIN Query

The INNER JOIN query is used to retrieve the matching records from two or more tables based on the specified condition.

Syntax:

SELECT table1.column_name(s), table2.column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;

For the demo purpose, we will use the following Employee and Department tables in all examples.

Employee Table
EmpIdFirstNameLastNameEmailSalaryDeptId
1'John''King''[email protected]'330001
2'James''Bond'
3'Neena''Kochhar''[email protected]'170002
4'Lex''De Haan''[email protected]'150001
5'Amit''Patel'180003
6'Abdul''Kalam''[email protected]'250002
Department Table
DeptIdName
1'Finance'
2'HR'

Consider the following inner join query.

SQL Script: Inner Join Query
SELECT Employee.EmpId, Employee.FirstName, Employee.LastName, Department.Name FROM Employee INNER JOIN Department ON Employee.DeptId = Department.DeptId;

The above inner join query joins the Employee table and Department table and retrieves records from both the tables where Employee.DeptId = Department.DeptId. It only fetches records from both the tables where DeptId in the Employee table matches with the DeptId of the Department table. If the DeptId is NULL or not matching, then it won't retrieve those records. The following is the result of the above query.

Employee Table
EmpIdFirstNameLastNameName
1'John''King''Finance'
3'Neena''Kochhar''HR'
4'Lex''De Haan''Finance'
6'Abdul''Kalam''HR'

Notice that it only displayed the records whose DeptId matches, not whose DeptId is null or not matching.

It does not matter which table you take first in the query. The following query will display the same result as above.

SQL Script: Inner Join Query
SELECT Employee.EmpId, Employee.FirstName, Employee.LastName, Department.Name FROM  Department INNER JOIN Employee ON Department.DeptId = Employee.DeptId;

It is not mandatory to use the INNER JOIN phrase. You can use the WHERE clause to achieve the same result, as shown below.

SQL Script: Inner Join using WHERE Clause
SELECT emp.EmpId, emp.FirstName, emp.LastName, dept.Name FROM  Department dept, Employee emp WHERE dept.DeptId = emp.DeptId;

You can take alias as short cuts of table names, as shown below.

SQL Script: Inner Join Query
SELECT emp.EmpId, emp.FirstName, emp.LastName, dept.Name FROM  Department dept INNER JOIN Employee emp ON dept.DeptId = emp.DeptId;

Repeat the INNER JOIN.. ON statement to include one more table in the query. For example, the following inner join query joins the three tables.

SQL Script: Inner Join Query
SELECT Employee.EmpId, Employee.FirstName, Employee.LastName, Consultant.FirstName, Department.Name FROM Employee INNER JOIN Department ON Employee.DeptId = Department.DeptId INNER JOIN Consultant ON Consultant.DeptId = Department.DeptId;
TUTORIALSTEACHER.COM

TutorialsTeacher.com is your authoritative source for comprehensive technologies tutorials, tailored to guide you through mastering various web and other technologies through a step-by-step approach.

Our content helps you to learn technologies easily and quickly for learners of all levels. By accessing this platform, you acknowledge that you have reviewed and consented to abide by our Terms of Use and Privacy Policy, designed to safeguard your experience and privacy rights.

[email protected]

ABOUT USTERMS OF USEPRIVACY POLICY
copywrite-symbol

2024 TutorialsTeacher.com. (v 1.2) All Rights Reserved.