SQL Server Left Join With Count + Examples

In this SQL Server tutorial, we will learn and understand how to use the SQL Server COUNT function with the LEFT JOIN clause on two tables in a query.

Recently, I was working again on the Employee Management System Database where I got the information on how to use SQL Server LEFT JOIN clause with the COUNT function in SQL Server.

So, here is the list of the topics that we are going to cover:

• How to use SQL Server Left Join With Count
• Selecting SQL server Left Join With Count Top 1
• Using SQL Server Left Join With Count Having Clause
• Using SQL Server Left Join With Count Distinct
• How to use SQL Server Left Join With Count Group By
• Using SQL Server Left Join With Count & Where Clause
• SQL Server Left Join With Count Not Returning Nulls
• How to use SQL Server Left Join With Count Year
• Use SQL Server Left Join With Count Yesterday

SQL Server Left Join With Count

The SQL Server LEFT JOIN clause is used to return all records from the TABLE_1 (right table) and matching records from the TABLE_2 (left table). And the COUNT function is used to return the total count of all records from the expression or column_name of the table in the result set.

SYNTAX:

SELECT COUNT(TABLE_1),
TABLE_2.SELECTED_COLUMN
FROM TABLE_1
LEFT JOIN TABLE_2
ON TABLE_1.COMMON_COLUMN=TABLE_2.COMMON_COLUMN
GROUP BY [ TABLE_1 | TABLE_2]. COLUMN_NAME;

EXAMPLE:

SELECT COUNT(EMPLOYEES.EMP_ID) AS TOTAL_COUNT,
JOB_TITLE.PROFILE
FROM EMPLOYEES
LEFT JOIN JOB_TITLE
ON EMPLOYEES.JOB_ID=JOB_TITLE.JOB_ID
GROUP BY JOB_TITLE.PROFILE;

With the help of the LEFT JOIN clause, the SELECT statement will retrieve and count the total number of employees based on the job profile from both tables i.e.; EMPLOYEES and JOB_TITLES.

And to shorter the function name, we have used the ALIAS clause with the AS keyword and given the name as TOTAL_COUNT for the output column_name in the result set.

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

Read: SQL Server OUTER JOIN

SQL Server Left Join With Count Top 1

In SQL Server, the SELECT TOP clause is used to specify the number of records to be fetched from a table. And it is normally used in a large table with thousand of records.

If we return a number of records then it will impact the performance of the result set.

Note that, not all database systems support the SELECT TOP clause.

EXAMPLE:

SELECT TOP 1 COUNT(JOB_TITLE.JOB_ID) AS TOTAL_COUNT,
DEPARTMENT.DEPARTMENT_NAME
FROM JOB_TITLE
LEFT JOIN DEPARTMENT
ON JOB_TITLE.DEPT_ID=DEPARTMENT.DEPT_ID
GROUP BY DEPARTMENT.DEPARTMENT_NAME;

As we see in the above query example, the SELECT statement retrieves the first record of the job title based on the department name from both tables i.e.; JOB_TITLE and DEPARTMENT. And it is done based on records retrieved from the JOB_TITLE and matching records from the DEPARTMENT table.

We hope that you have understood how to use SQL Server COUNT TOP 1 clause with LEFT JOIN clause on two tables in a query.

Read: RIGHT JOIN in SQL Server

SQL Server Left Join With Count Having Clause

Since the WHERE keyword cannot be used with aggregate functions in SQL Server, the HAVING clause was added to SQL Server.

EXAMPLE:

SELECT COUNT(JOB_TITLE.PROFILE) AS TOTAL_COUNT,
DEPARTMENT.DEPARTMENT_NAME
FROM JOB_TITLE
LEFT JOIN DEPARTMENT
ON JOB_TITLE.DEPT_ID=DEPARTMENT.DEPT_ID
GROUP BY DEPARTMENT.DEPARTMENT_NAME
HAVING COUNT(JOB_TITLE.PROFILE) > 1;

With the help of the SELECT statement, it returns the total count of the job profile that is greater than 1 based on the department name in the result set.

We hope that you have understood how to use SQL Server COUNT with the LEFT JOIN and HAVING clauses on two tables in a query.

Read: What is SQL Server Cross Join?

SQL Server Left Join With Count Distinct

In SQL Server, the SELECT DISTINCT statement is used to return distinct values from expression or column_name in the result set.

EXAMPLE:

SELECT COUNT(DISTINCT MANAGERS.MANAGER_NAME) AS TOTAL_MANAGERS,
JOB_TITLE.PROFILE
FROM MANAGERS
LEFT JOIN JOB_TITLE
ON MANAGERS.DEPT_ID=JOB_TITLE.DEPT_ID
GROUP BY JOB_TITLE.PROFILE;

In the query example, the SELECT statement brings a unique manager’s name based on the job profile from both tables i.e.; MANAGERS and JOB_TITLE tables. And based on the DEPT_ID as a common column, the LEFT JOIN retrieves all records from the MANAGERS and matches records from the JOB_TITLE table in the result set.

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

Read: SQL Server Outer Join With Count

SQL Server Left Join With Count Group By

In SQL Server, the GROUP BY clause is used to group rows with the same values into summary rows like “to find manager names per department in an organization”. Additionally, it is also used to group the result set by one or more columns with aggregate functions like COUNT(), MAX(), MIN(), SUM(), and AVG().

EXAMPLE:

SELECT COUNT(MANAGERS.MANAGER_NAME) AS TOTAL_MANAGERS,
JOB_TITLE.PROFILE
FROM MANAGERS
LEFT JOIN JOB_TITLE
ON MANAGERS.DEPT_ID=JOB_TITLE.DEPT_ID
GROUP BY JOB_TITLE.PROFILE;

With the help of the LEFT JOIN clause, the SELECT statement retrieves and returns manager names in the TOTAL_MANAGERS column which is done based on the job profile and grouped them in the resultset.

We hope that you now fully grasp how to use the SQL Server COUNT function on two tables with LEFT JOIN and GROUP BY clauses.

SQL Server Left Join With Count & Where Clause

Here we will see an example of the SQL Server COUNT with the LEFT JOIN and the WHERE condition on tables by the following query:

SELECT COUNT ( * ) AS TOTAL_COUNT
FROM STAFF
LEFT JOIN SALES_TEAM
ON STAFF.ID=SALES_TEAM.STAFF_ID
WHERE STAFF.ID>5

Based on the LEFT JOIN clause, the SELECT COUNT statement returns the total count of records from both tables.

In the WHERE condition, it filters the records of the ID column and finds the value greater than or equal to 5 from the STAFF table for the result set.

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

SQL Server Left Join With Count Not Returning Nulls

Let’s see an example of the SQL Server COUNT function with the LEFT JOIN and IS NOT NULL condition on tables by the following query:

EXAMPLE:

SELECT COUNT( * ) AS TOTAL_COUNT
FROM STAFF
LEFT JOIN SALES_TEAM
ON STAFF.ID=SALES_TEAM.STAFF_ID
WHERE STAFF.ID IS NOT NULL;

With the help of the LEFT JOIN query example, we counted all records from both tables i.e.; STAFF and SALES_TEAM by the WHERE condition.

In the WHERE condition, we filtered the records of the STAFF_ID column by NOT NULL value so that we can count all records from both tables in the resultset.

We hope you have gained an understanding of how to use the COUNT function of SQL Server to bring non-null values from both tables using the LEFT JOIN clause.

SQL Server Left Join With Count Year

The following SQL Server COUNT YEAR functions with the LEFT JOIN will be utilised by the two tables i.e.; HARVARD_UNIVERSITY and TEACHER_OF_HARVARDUNIVERSITY:

EXAMPLE:

SELECT COUNT(YEAR(HARVARD_UNIVERSITY.STUDENT_ADMITDATE)) AS TOTAL_YEARCOUNT
FROM HARVARD_UNIVERSITY
LEFT JOIN TEACHER_OF_HARVARDUNIVERSITY
ON HARVARD_UNIVERSITY.STUDENT_ID=TEACHER_OF_HARVARDUNIVERSITY.TEACHER_ID;

With the help of the LEFT JOIN clause, we extracted the year portion value of the STUDENT_ADMITDATE column and then counted its year value of the same column from both tables i.e.; HARVARD_UNIVERSITY and TEACHER_OF_HARVARDUNIVERSITY.

Hopefully, you now understand how to count the YEAR value from both tables using the SQL Server LEFT JOIN clause.

SQL Server Left Join With Count Yesterday

There is no function such as the YESTERDAY function in SQL Server. However, MariaDB has a method called “YESTERDAY.” The DATEADD function on the tables can be used to define the YESTERDAY value as follows:

EXAMPLE:

USE SQLSERVERGUIDES;

SELECT COUNT(DATEADD(DAY,-1,HARVARD_UNIVERSITY.STUDENT_ENDDATE)) AS YESTERDAY_DAYCOUNT
FROM HARVARD_UNIVERSITY
LEFT JOIN TEACHER_OF_HARVARDUNIVERSITY
ON HARVARD_UNIVERSITY.STUDENT_ID=TEACHER_OF_HARVARDUNIVERSITY.TEACHER_ID;

With the help of the LEFT JOIN in the SELECT statement, we extracted the previous day of the student_enddate column. And counted its value from both tables i.e.; HARVARD_UNIVERSITY and TEACHER_OF_HARVARDUNIVERSITY.

With this query, we have shared an example of SQL Server COUNT and DATEADD functions with the LEFT JOIN clause.

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

• SQL Server Drop Trigger If Exists
• Delete From With Join in SQL Server
• SQL Server Trigger Update with Examples
• SQL Server Trigger After Insert Update

We now know about the post “SQL Server Left Join With Count” after reading this tutorial. Here is a list of each subtopic we covered to help you understand the concept.

• How to use SQL Server Left Join With Count
• Selecting SQL server Left Join With Count Top 1
• Using SQL Server Left Join With Count Having Clause
• Using SQL Server Left Join With Count Distinct
• How to use SQL Server Left Join With Count Group By
• Using SQL Server Left Join With Count & Where Clause
• SQL Server Left Join With Count Not Returning Nulls
• How to use SQL Server Left Join With Count Year
• Use SQL Server Left Join With Count Yesterday