SQL Inner Join

SQL INNER JOIN is used to combine rows from two or more tables based on a related column. It returns only the rows that have matching values in both tables, filtering out non-matching records.

• Combines data from multiple tables
• Returns only matching records
• Filters out non-matching rows
• Commonly used in relational databases
• Useful for working with related data

Syntax:

SELECT columns
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

Key Terms

• columns: The specific columns we want to retrieve.
• table1 and table2: The tables being joined.
• column_name: The columns used for matching values.

Example of SQL INNER JOIN

The professor table stores details about professors, while the teacher table contains information about the courses they teach. These two tables are linked through a common column: ID in the professor table and prof_id in the teacher table.

professor Table

teacher Table

We want to retrieve the course_id, prof_id, professor’s Name, and Salary by joining the professor and teacher tables. The query joins the professor table and the teacher table based on the condition that the ID from the professor table matches the prof_id in the teacher table.

Query

SELECT teacher.course_id, teacher.prof_id, professor.Name, professor.Salary
FROM professor INNER JOIN teacher ON professor.ID = teacher.prof_id;

Output

Explanation:

The output contains the details of professors and the courses they teach. The INNER JOIN operation ensures that only the records where a professor is assigned a course are included in the result. The professor who does not teach a course (like Aryan, who is not listed in the output) is excluded.

Difference Between INNER JOIN and OUTER JOIN