Inner Join/Simple Join
In an INNER join, it allows retrieving data from two tables with the same ID.
An Inner Join returns only the matching rows between the two tables based on a specified condition. It combines data from two tables based on a common column between them, which is specified using the ON keyword in SQL. Only the rows that meet the join condition from both tables are returned. If a row in one table does not have a matching row in the other table, that row will not be included in the result set.
Syntax:
SELECT COLUMN1, COLUMN2 FROM
[TABLE 1] INNER JOIN [TABLE 2]
ON Condition;
The following is a join query that shows the names of students enrolled in different courses.
Query:
SELECT StudentCourse.CourseID,Student.StudentName FROM Student INNER JOIN StudentCourse ON StudentCourse.EnrollNo = Student.EnrollNo ORDER BY StudentCourse.CourseID;
Note: Inner is optional above. Simple JOIN is also considered as INNER JOIN The above query would produce the following result.
CourseID | StudentName |
---|---|
1 | geek1 |
1 | geek2 |
2 | geek1 |
2 | geek3 |
3 | geek1 |
Example:
For example, let’s say we have two tables, Table1 and Table2, with the following data:
Table 1
ID | Name |
---|---|
1 | John |
2 | Sarah |
3 | David |
Table 2
ID | Address |
---|---|
1 | 123 Main St. |
2 | 456 Elm St. |
4 | 789 Oak St. |
If we perform an Inner Join on these tables using the ID column, the result set would only include the matching rows from both tables, which are the rows with ID values of 1 and 2:
Query:
SELECT Table1.ID, Table 1. Name, Table 2.Address FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID
Output:
ID | Name | Address |
---|---|---|
1 | John | 123 Main St. |
2 | Sarah | 456 Elm St. |
Inner Join vs Outer Join
Inner Join and Outer Join are the types of join. The inner join has the work to return the common rows between the two tables, whereas the Outer Join has the work of returning the work of the inner join in addition to the rows which are not matched.
Let’s discuss both of them in detail in this article. Before moving ahead, let’s discuss what is Join in SQL.
In a relational database management system (RDBMS), there are different types of joins that can be used to combine data from two or more tables in a database. The two most common types of joins are Inner Join and Outer Join.