SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL OPERATORS SQL COMMANDS SQL Queries

SQL Table

SQL TABLE SQL CREATE TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL UPDATE TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT INTO Statement

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL

SQL Inner Join

In Structured Query Language, the most used join query is the Inner join query.

Inner join query retrieves the records from one or more tables with similar data or records. The joins condition returns only common rows between the tables.

Inner Join is one of the types of joins in the Structured Query Language Join.

Suppose we have two tables, Table A and Table B. Table A have some columns, and Table B also has some columns. How joining will happen between these two-columns? In this case, there is one common column between these two tables. So joining will be met through this common column.

Syntax of Inner Join in SQL:

SELECT COLUMNNAME1, COLUMNNAME2, COLUMNNAME3, COLUMNNAME4 FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.COLUMN NAME = TABLE2.COLUMN NAME;

Example of Inner Join in Structured Query Language:

Consider these tables to understand the Inner Join concept.

Table 1: Employees:

EMPLOYEE_IDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGERID
1001VAIBHAVIMISHRA65500PUNEORACLE1
1002VAIBHAVSHARMA60000BANGALOREC#4
1003NIKHILVANI50500HYDERABADFMW2
2001PRACHISHARMA55500CHANDIGARHORACLE1
2002BHAVESHJAIN65500PUNEFMW2
2003RUCHIKAJAIN50000MUMBAIC#4
3001PRANOTISHENDE55500PUNEJAVA3
3002ANUJAWANRE50500HYDERABADFMW2
3003DEEPAMJAUHARI58500MUMBAIJAVA3
4001RAJESHGOUD60500MUMBAITESTING5
4002ASHWINIBAGHAT54500BANGALOREJAVA3
4003RUCHIKAAGARWAL60000DELHIORACLE1
5001ARCHITSHARMA55500DELHITESTING5
5002RAKESHKUMAR70000CHANDIGARHC#4
5003MANISHSHARMA62500BANGALORETESTING5

Table 2: Manager.

Manager_IdManager_NameManager_Department
1Kirti ChaudharyORACLE
2Manish BafnaFMW
3Snehdeep KaurJAVA
4Satish KumarC#
5Anupam MishraTESTING

Example 1: Execute an inner join query on the employee and manager table name.

SELECT Employee_Id, First_Name, Last_Name, Salary, City, Department, M.Manager_Id, Manager_Name FROM Employees E INNER JOIN Manager M ON E.Manager_Id = M.Manager_Id;

The above Inner join query joins the employees' table and manager table and retrieves the data from the tables where E.Manager_Id = M.Manager_Id.

The output of the above query is as follows:

EMPLOYEE_IDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGER_IDMANAGER_NAME
1001VAIBHAVIMISHRA65500PUNEORACLE1Kirti Chaudhary
1002VAIBHAVSHARMA60000BANGALOREC#4Satish Kumar
1003NIKHILVANI50500HYDERABADFMW2Manish Bafna
2001PRACHISHARMA55500CHANDIGARHORACLE1Kirti Chaudhary
2002BHAVESHJAIN65500PUNEFMW2Manish Bafna
2003RUCHIKAJAIN50000MUMBAIC#4Satish Kumar
3001PRANOTISHENDE55500PUNEJAVA3Snehdeep Kaur
3002ANUJAWANRE50500HYDERABADFMW2Manish Bafna
3003DEEPAMJAUHARI58500MUMBAIJAVA3Snehdeep Kaur
4001RAJESHGOUD60500MUMBAITESTING5Anupam Mishra
4002ASHWINIBAGHAT54500BANGALOREJAVA3Snehdeep Kaur
4003RUCHIKAAGARWAL60000DELHIORACLE1Kirti Chaudhary
5001ARCHITSHARMA55500DELHITESTING5Anupam Mishra
5002RAKESHKUMAR70000CHANDIGARHC#4Satish Kumar
5003MANISHSHARMA62500BANGALORETESTING5Anupam Mishra
SQL Inner Join

Example 2: Execute an inner join query on table name Employees and Manager using order by clause.

SELECT Employee_Id, First_Name, Last_Name, Salary, City, Department, M.Manager_Id, Manager_Name FROM Employees E INNER JOIN Manager M ON E.Manager_Id = M.Manager_Id ORDER BY Manager_Id;

The above inner join query joins the two tables named Employees Table and Manager Table and retrieves the data from both the tables where E.MANAGER_ID = M.MANAGER_ID. The employees' records will be displayed in the ascending order by Manager_Id as we have used the ORDER BY clause in the query. The query will display only similar records between the two tables.

The output of the above query is as follows:

EMPLOYEE_IDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGER_IDMANAGER_NAME
1001VAIBHAVIMISHRA65500PUNEORACLE1Kirti Chaudhary
2001PRACHISHARMA55500CHANDIGARHORACLE1Kirti Chaudhary
4003RUCHIKAAGARWAL60000DELHIORACLE1Kirti Chaudhary
2002BHAVESHJAIN65500PUNEFMW2Manish Bafna
3002ANUJAWANRE50500HYDERABADFMW2Manish Bafna
1003NIKHILVANI50500HYDERABADFMW2Manish Bafna
4002ASHWINIBAGHAT54500BANGALOREJAVA3Snehdeep Kaur
3003DEEPAMJAUHARI58500MUMBAIJAVA3Snehdeep Kaur
3001PRANOTISHENDE55500PUNEJAVA3Snehdeep Kaur
2003RUCHIKAJAIN50000MUMBAIC#4Satish Kumar
5002RAKESHKUMAR70000CHANDIGARHC#4Satish Kumar
1002VAIBHAVSHARMA60000BANGALOREC#4Satish Kumar
5001ARCHITSHARMA55500DELHITESTING5Anupam Mishra
5003MANISHSHARMA62500BANGALORETESTING5Anupam Mishra
4001RAJESHGOUD60500MUMBAITESTING5Anupam Mishra
SQL Inner Join

Example 3: Execute an inner join query on table name Employees and Manager using group by clause.

SELECT Employee_Id, First_Name, Last_Name, Salary, City, Department, M.Manager_Id, Manager_Name FROM Employees E INNER JOIN Manager M ON E.Manager_Id = M.Manager_Id GROUP By Salary;

The above inner join query joins the two Employees Table and Manager Table and retrieves the data from the tables where E.MANAGER_ID = M.MANAGER_ID. The data is displayed in the GROUP BY employee salary. Employees with the same salary will be in a group with one salary. Employees who don't have the same salary number as another employee's salary, will have separate salary group.

The output of the above query is as follows:

EMPLOYEE_IDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGER_IDMANAGER_NAME
2003RUCHIKAJAIN50000MUMBAIC#4Satish Kumar
1003NIKHILVANI50500HYDERABADFMW2Manish Bafna
4002ASHWINIBAGHAT54500BANGALOREJAVA3Snehdeep Kaur
2001PRACHISHARMA55500CHANDIGARHORACLE1Kirti Chaudhary
3003DEEPAMJAUHARI58500MUMBAIJAVA3Snehdeep Kaur
1002VAIBHAVSHARMA60000BANGALOREC#4Satish Kumar
4001RAJESHGOUD60500MUMBAITESTING5Anupam Mishra
5003MANISHSHARMA62500BANGALORETESTING5Anupam Mishra
1001VAIBHAVIMISHRA65500PUNEORACLE1Kirti Chaudhary
5002RAKESHKUMAR70000CHANDIGARHC#4Satish Kumar
SQL Inner Join

Example 4: Execute an inner join query on table name Employees and Manager using where clause.

SELECT Employee_Id, First_Name, Last_Name, Salary, City, Department, M.Manager_Id, Manager_Name FROM Employees E INNER JOIN Manager M ON E.Manager_Id = M.Manager_Id WHERE Salary > 52000;

The above inner join query joins the two Employees Table and Manager Table and retrieves the data from the tables where E.MANAGER_ID = M.MANAGER_ID. The query will display only employee's data where the employee salary is greater than 52000.

The output of the above query is as follows:

EMPLOYEE_IDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGER_IDMANAGER_NAME
1001VAIBHAVIMISHRA65500PUNEORACLE1Kirti Chaudhary
1002VAIBHAVSHARMA60000BANGALOREC#4Satish Kumar
2001PRACHISHARMA55500CHANDIGARHORACLE1Kirti Chaudhary
2002BHAVESHJAIN65500PUNEFMW2Manish Bafna
3001PRANOTISHENDE55500PUNEJAVA3Snehdeep Kaur
3003DEEPAMJAUHARI58500MUMBAIJAVA3Snehdeep Kaur
4001RAJESHGOUD60500MUMBAITESTING5Anupam Mishra
4002ASHWINIBAGHAT54500BANGALOREJAVA3Snehdeep Kaur
4003RUCHIKAAGARWAL60000DELHIORACLE1Kirti Chaudhary
5001ARCHITSHARMA55500DELHITESTING5Anupam Mishra
5002RAKESHKUMAR70000CHANDIGARHC#4Satish Kumar
5003MANISHSHARMA62500BANGALORETESTING5Anupam Mishra
SQL Inner Join



ADVERTISEMENT
ADVERTISEMENT