INNER JOIN ON vs WHERE clause in SQL Server

In SQL Server, joining tables and filtering data are essential for retrieving meaningful information. The INNER JOIN operation is used to combine rows from multiple tables based on a matching condition, while the WHERE clause allows for further filtering of results.

In this article, we will PL/SQL Show Indexes by understanding its syntax and various practical examples.

What is INNER JOIN ON?

The INNER JOIN in SQL Server is used to retrieve rows from two or more tables where there is a match on a specified condition. It returns only the rows where there is a match between the columns in the joined tables.

The ON clause specifies the condition for the join, defining the relationship between the tables and ensuring that only rows satisfying this condition are included in the result set.

Syntax:

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

Key terms:

• column_list: This specifies the columns you want to retrieve from the joined tables.
  
• table1,table2: Tables from where we want to fetch the records.
  
• column_name: corresponding column in each table that holds the same data type and contains the values for comparison.

What is WHERE clause?

The WHERE clause in SQL is used to filter records that meet specific criteria, thereby reducing the number of rows returned by a query. It allows you to specify conditions that must be satisfied for data to be included in the result set.

The WHERE clause can be used with SELECT, UPDATE or DELETE statements and supports logical operators such as AND, OR, and NOT to create more complex conditions.

Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Key terms:

• column_list: This specifies the columns you want to retrieve from the table.
  
• table_name: Table from where we want to fetch the records.
  
• condition: The condition is a logical expression that evaluates to true, false, or unknown. It defines the criteria that the rows must meet to be included in the result set. The condition can consist of one or multiple expressions connected by logical operators (such as AND, OR, NOT).

Examples of INNER JOIN ON vs WHERE clause in SQL Server

Assume we have two tables, Students and Courses, and we want to retrieve student names and their enrolled courses along with the instructor names.

Students Table

The CREATE TABLE statement creates a table named Students with columns for StudentID, Name, and Major. The INSERT INTO statements add records for four students, specifying their IDs, names, and majors.

Query:

CREATE TABLE Students (
  StudentID INT PRIMARY KEY,
  Name VARCHAR(50) NOT NULL,
  Major VARCHAR(10) NOT NULL
);

INSERT INTO Students (StudentID, Name, Major)
VALUES (1, 'Alice', 'CS'),
       (2, 'Bob', 'IT'),
       (3, 'Charlie', 'EE'),
       (4, 'David', 'CS');

Output:

Explanation:

The output shows the content of the Students table, listing each student’s ID, name, and their academic major. This setup is used to store and manage student information.

Courses Table

The CREATE TABLE statement creates a table named Courses with columns for CourseID, Name, Instructor, and StudentID. The FOREIGN KEY constraint ensures that the StudentID in the Courses table references a valid ID from the Students table. The INSERT INTO statements add records for various courses, each associated with a student

Query:

CREATE TABLE Courses (
  CourseID INT PRIMARY KEY,
  Name VARCHAR(255) NOT NULL,
  Instructor VARCHAR(50) NOT NULL,
  StudentID INT,
  FOREIGN KEY (StudentID) REFERENCES Students(StudentID)
);

INSERT INTO Courses (CourseID, Name, Instructor, StudentID)
VALUES (101, 'Introduction to CS', 'Prof. Miller', 1),
       (101, 'Introduction to CS', 'Prof. Miller', 4),  -- Duplicate course for David
       (102, 'Intro to Web Dev', 'Prof. Jones', 3),
       (103, 'Advanced Programming', 'Prof. Smith', 4),
       (201, 'Introduction to IT', 'Prof. Brown', 2),
       (202, 'Network Security', 'Prof. Smith', 3);

Output:

Explanation:

The output displays each course's ID, name, instructor, and the ID of the student enrolled, showing which students are taking which courses.

Example 1: Using WHERE Clause with INNER JOIN (Filtering After Joining)

Query:

SELECT s.Name, c.Name AS CourseName, c.Instructor
FROM Students s
INNER JOIN Courses c ON s.StudentID = c.StudentID
WHERE s.Major = 'CS';

Output:

Explanation:

This query first performs an inner join on StudentID to combine data from the Students and Courses tables. It then filters the results to include only students with the major 'CS'. As a result, all students are joined first, including those who are not CS majors, and then the filter is applied.

Example 2: Using INNER JOIN ON Clause (Filtering During Joining)

Query:

SELECT s.Name, c.Name AS CourseName, c.Instructor
FROM Students s
INNER JOIN Courses c ON s.StudentID = c.StudentID
    AND s.Major = 'CS';

Output:

Explanation:

This query incorporates the major filter directly within the ON clause of the inner join. Only students with the major 'CS' are considered for joining with courses, which can lead to better performance by reducing the amount of data processed during the join operation.

INNER JOIN ON vs WHERE clause in SQL Server

Conclusion

In conclusion, the INNER JOIN ON in SQL Server is different from the WHERE clause though they are used together for similar purposes. INNER JOIN ON is used mainly for joining the rows of two or more tables with the help of a related column and can be more efficient and expressive in comparison with using JOIN.

The WHERE clause on the other hand specifies conditions in which the query results are to be retrieved from the database making it very important when refining and excluding data. Although both can at times produce similar results when used in isolation the proper usage according to the type leads to the best efficiency of queries as well as the best maintainability.