inner join in sql and DBMS

Inner join shows results from both tables where there is any match between columns in both tables.

RegistrationNo Marks
T4Tutorials1 77
T4Tutorials2 32
T4Tutorials3 88

Table: Exams

Inner join shows results from both tables where there is any match between columns in both tables.

RegistrationNo Fee
T4Tutorials1 1000
T4Tutorials2 2000
T4Tutorials3 3000

Table: Accounts

Now, we have two different tables. If we want to show the record of both tables in one single table then we can use inner join to join both two tables.

Query of an inner join

SELECT Exams.RegistrationNo, Exams.Marks, Accounts.Fee FROM Exams INNER JOIN Accounts ON Exams.RegistrationNo =  Accounts.RegistrationNo

RegistrationNo Marks Fee
T4Tutorials1 77 1000
T4Tutorials2 32 2000
T4Tutorials3 88 3000

Table: Table generated after INNER Join

Comparison of inner-join with other joins

Type Syntax Syntax What will do?
Inner Join SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column; SELECT * FROM Student_Table INNER JOIN Teacher_Table ON Student_Table.ClassID = Teacher_Table.ClassID; Show only the rows where there is a match in both tables based on the specified condition.
Semi Join SELECT * FROM table1 WHERE EXISTS (SELECT 1 FROM table2 WHERE table1.column = table2.column); SELECT * FROM Student_Table WHERE EXISTS (SELECT 1 FROM Teacher_Table WHERE Student_Table.ClassID = Teacher_Table.ClassID); Show only the rows from the left table where there is a match in the right table.
Cross Join SELECT * FROM table1 CROSS JOIN table2; SELECT * FROM Student_Table CROSS JOIN Teacher_Table; Show the Cartesian product of the two tables, resulting in all possible combinations of rows.
Full (Outer) Join SELECT * FROM table1 FULL JOIN table2 ON table1.column = table2.column; SELECT * FROM Student_Table FULL JOIN Teacher_Table ON Student_Table.ClassID = Teacher_Table.ClassID; Show all rows when there is a match in either the left or right table.
Left (Outer) Join SELECT * FROM table1 LEFT JOIN table2 ON table1.column = table2.column; SELECT * FROM Student_Table LEFT JOIN Teacher_Table ON Student_Table.ClassID = Teacher_Table.ClassID; Show all rows from the left table and the matched rows from the right table.
Right (Outer) Join SELECT * FROM table1 RIGHT JOIN table2 ON table1.column = table2.column; SELECT * FROM Student_Table RIGHT JOIN Teacher_Table ON Student_Table.ClassID = Teacher_Table.ClassID; Show all rows from the right table and the matched rows from the left table.
Self Join SELECT * FROM table1 t1 INNER JOIN table1 t2 ON t1.column = t2.column; SELECT * FROM Student_Table t1 INNER JOIN Student_Table t2 ON t1.ClassID = t2.ClassID; Joins a table with itself, treating it as if it were two separate tables.
Test Your Understandings

1.Inner join shows all rows from all tables ? YES / NO

Answer - Click Here:
No, its show only matching rows

Topic Covered

inner join in sql and DBMS