SQL Server Outer Join With Count

In this SQL Server tutorial, we will learn how to use OUTER JOIN with the COUNT() function in SQL Server. Additionally, we will discuss the following topics related to it

How to use the SQL Server COUNT function with the OUTER JOIN conditions on the table?
How to use the SQL Server COUNT function with the LEFT JOIN condition on the table?
How to use the SQL Server COUNT function and the WHERE condition with the OUTER JOIN conditions on tables?
How to use the SQL Server COUNT DISTINCT function with the OUTER JOIN condition on tables?
How to use the SQL Server COUNT function and GROUP BY clause with the OUTER JOIN conditions on tables by the query?
How to create a view on tables and use the COUNT function with the OUTER JOIN condition?
How to use the SQL Server COUNT and YEAR functions on tables with the OUTER JOIN conditions?

Table of Contents

SQL Server Count

The SQL Server COUNT function is used to count the total number of rows of an expression or column by the query. Here is the syntax of the SQL Server COUNT function on the table by the following query:

SELECT COUNT (EXPRESSION | COLUMN_NAME )
FROM TABLE_NAME;

EXAMPLE:

SELECT COUNT(*) AS TOTAL_COUNT
FROM MIT_UNIVERSITY;

The SELECT COUNT statement returns the total count of all records from the MIT_UINVERSITY table.

Sql server count example — Example of SQL Server COUNT Function

SQL Server Outer Join With Count

In SQL Server, the aggregate_functions are used to summarize data over groups of records from a query result set. So, when we use the aggregate_function like COUNT with a ‘*’ or ‘1’ parameter value, we may get a different resultset when implemented with the LEFT JOIN or INNER JOIN condition.

The SQL Server OUTER JOIN is used to return all records from both tables when there is a match between left table_1 or right table_2. Note that the FULL OUTER JOIN or OUTER JOIN is the same. Normally the SQL Server FULL OUTER JOIN returns a very large set of records from the result set.

Here is the image of the SQL Server FULL OUTER JOIN condition:

Sql server full outer join image — Image of SQL Server FULL OUTER JOIN condition

Here are two tables (MIT_UNIVERSITY and MITS_TEACHER) that are used for the FULL OUTER JOIN clause:

Sql server outer join with count — MIT_UNIVERSITY

EXAMPLE:

SELECT COUNT (*) AS TOTAL_COUNT 
FROM MIT_UNIVERSITY
FULL OUTER JOIN MITS_TEACHER
ON MIT_UNIVERSITY.STUDENT_ID=MITS_TEACHER.TEACHER_ID;

With the help of the FULL OUTER JOIN clause, the SELECT COUNT statement retrieves and gives the total count of all records from both tables, i.e., the MIT_UNIVERSITY and MITS_TEACHER tables.

Sql server outer join with count tutorial — Example of SQL Server OUTER JOIN with COUNT

We hope that you have understood how to use the SQL Server COUNT function with the OUTER JOIN clause on tables by the query.

Read: Temp table in stored procedure in SQL Server

SQL Server Left Join Count

In SQL Server, the LEFT JOIN condition is used to return all records from the left table (TABLE_1) and the matching records from the right table (TABLE_2). If there is no match, there are no records displayed on the right side.

If you want more detail of SQL Server LEFT JOIN, kindly look into this link “SQL Server LEFT JOIN tutorial”.

EXAMPLE:

SELECT COUNT (*) AS TOTAL_COUNT 
FROM MIT_UNIVERSITY
LEFT OUTER JOIN MITS_TEACHER
ON MIT_UNIVERSITY.STUDENT_ID=MITS_TEACHER.TEACHER_ID;

By using the LEFT OUTER JOIN clause, the SELECT COUNT statement returns the number of matching records from the MIT_UNIVERSITY table in comparison to the MITS_TEACHER table.

Sql server left join count group by example — Example of SQL Server COUNT function with the LEFT JOIN condition on the tables

We hope that you have understood how to use SQL Server COUNT function with LEFT OUTER JOIN clause on tables by the query.

Read: Stored procedure in SQL Server for insert and update

SQL Server Outer Join With Count And Where Clause

Here we will learn and understand how to use the SQL Server COUNT function with the OUTER JOIN and WHERE conditions on the table by the T-SQL query.

The SQL Server WHERE condition is used to extract only those records which met the condition.

EXAMPLE:

SELECT COUNT(MIT_UNIVERSITY.STUDENT_FIRSTNAME) 
FROM MIT_UNIVERSITY
FULL OUTER JOIN HARVARD_UNIVERSITY
ON MIT_UNIVERSITY.STUDENT_ID=HARVARD_UNIVERSITY.STUDENT_ID
WHERE MIT_UNIVERSITY.GENDER='Male';

Based on the WHERE criteria, the SELECT COUNT command delivers the total count of all records from both tables. The GENDER column in the WHERE condition filters the resultset according to the Male record for the total count.

Sql server outer join with count and where clause — Example of SQL Server OUTER JOIN With COUNT And WHERE Clause

So, we hope you have understood how to use SQL Server COUNT function with FULL OUTER JOIN clause on both tables and also using the WHERE condition for the result set.

Read: SQL Server Datetime functions examples

SQL Server Outer Join With Count Distinct

The SQL Server DISTINCT clause is used to extract records without duplicated values from the expression or column_name by the query.

EXAMPLE:

SELECT COUNT ( DISTINCT MITS_TEACHER.TEACHER_NAME) AS TOTAL_COUNT 
FROM MIT_UNIVERSITY
LEFT OUTER JOIN MITS_TEACHER
ON MIT_UNIVERSITY.STUDENT_ID=MITS_TEACHER.TEACHER_ID;

Here we will understand the example explanation:

DISTINCT function: It returns non-duplicate values of the TEACHER_NAME column in the MITS_TEACHER table.
COUNT function: It returns the total count of the DISTINCT function by the query.
LEFT OUTER JOIN clause: It retrieves all records from the left_table (MIT_UNIVERSITY) with matching records from the right_table (MITS_TEACHER).

Sql server outer join with count distinct — Example of SQL Server OUTER JOIN with COUNT DISTINCT

We hope that you have understood how to use the SQL Server COUNT DISITNCT function with the OUTER JOIN clause on the tables by the query.

Read: Difference between table and view in SQL Server

SQL Server Outer Join Count View

In SQL Server, a VIEW is a virtual table based on the SQL statement resultset. And it contained rows and columns just like a real table. It also contains fields from one or more actual database tables.

A view can be extended with SQL statements and functions to present data as though it were drawn from a single table.

SYNTAX:

CREATE VIEW VIEW_NAME 
AS 
SELECT COLUMN_1, COUNT(COLUMN_NAME)
FROM TABLE_1
FULL OUTER JOIN TABLE_2
ON TABLE_1.COMMON_COLUMN=TABLE_2.COMMON_COLUMN;

EXAMPLE:

CREATE VIEW FULLOUTERJOIN
AS
SELECT COUNT ( *) AS THE_COUNT 
FROM MIT_UNIVERSITY
LEFT OUTER JOIN MITS_TEACHER
ON MIT_UNIVERSITY.STUDENT_ID=MITS_TEACHER.TEACHER_ID;

SELECT * FROM FULLOUTERJOIN;

First, we have created a view called FULLOUTERJOIN by using CREATE VIEW statement. With the help of CREATE VIEW statement, we have used the SELECT COUNT statement to return total records from both tables based on the LEFT OUTER JOIN clause.

If we want to retrieve all records from the FULLOUTERJOIN view, then we will use the SELECT statement.

We hope that you have understood the subtopic “SQL Server Outer Join Count View” by using the SQL Server CREATE VIEW statement to create a view on the tables and using the COUNT with the OUTER JOIN conditions by the query.

Read: Alter view in SQL Server

SQL Server Outer Join Count Year

The SQL Server YEAR function is used to extract the year portion value from the DATE or DATETIME column by the query. Here are two different tables used i.e; HARVARD_UNIVERSITY and TEACHER_OF_HARVARDUNIVERSITY for the YEAR function with the OUTER JOIN clause.

EXAMPLE:


SELECT COUNT (YEAR(HARVARD_UNIVERSITY.STUDENT_ENDDATE)) AS THE_COUNT 
FROM HARVARD_UNIVERSITY
LEFT OUTER JOIN TEACHER_OF_HARVARDUNIVERSITY
ON HARVARD_UNIVERSITY.STUDENT_ID=TEACHER_OF_HARVARDUNIVERSITY.TEACHER_ID;

Where,

YEAR function: It will extract the year portion value of the STUDENT_ENDDATE column from the HARVARD_UNIVERSITY table.
SELECT COUNT statement: It returns the total count of the YEAR function from both tables by using the LEFT OUTER JOIN clause.

Sql server outer join count year — Example of SQL Server OUTER JOIN clause with COUNT YEAR function

We trust you now understand how to utilize the COUNT YEAR functions in SQL Server along with the OUTER JOIN statement on the tables returned by the query.

You may also like to read the following SQL Server tutorials.

We now know how to use the SQL Server Outer Join With Count function after reading this lesson. We also discussed a few instances to help you comprehend the concept. Below is a list of all the topics we’ve covered.

How to use the SQL Server COUNT function with the OUTER JOIN conditions on the table?
How to use the SQL Server COUNT function with the LEFT JOIN condition on the table?
How to use the SQL Server COUNT function and the WHERE condition with the OUTER JOIN conditions on tables?
How to use the SQL Server COUNT DISTINCT function with the OUTER JOIN condition on tables?
How to use the SQL Server COUNT function and GROUP BY clause with the OUTER JOIN conditions on tables by the query?
How to create a view on tables and use the COUNT function with the OUTER JOIN condition?
How to use the SQL Server COUNT and YEAR functions on tables with the OUTER JOIN conditions?

Bijay

I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.

Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.