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
Topic Covered
inner join in sql and DBMS