Search⌘ K
Home
Courses
SQL Interview Preparation—Intermediate Level

Answer: Using CROSS JOIN

Find a detailed explanation of using CROSS JOIN in SQL.

We'll cover the following...

Solution

The solution is given below:

MySQL
-- The query to find employees with sales in each category
SELECT     E.EName AS 'Employee Name',
           PC.CategoryName AS 'Category Name',
           COUNT(S.SalesID) AS 'Sales Count'
FROM       Employees E
CROSS JOIN ProductCategories PC
LEFT JOIN  Sales S ON E.EID = S.EID AND S.CategoryID = PC.CategoryID
GROUP BY   E.EName, PC.CategoryName;

Code explanation

The explanation of the solution code is given below:

  • Lines 2–4: The SELECT statement selects the columns EName, CategoryName, and count of sales made using the aggregate function with the SalesID column. We use AS to set an alias for the columns and tables.

  • Line 5: The data is retrieved from the Employees table.

  • Line 6: The CROSS JOIN is performed between Employees and ProductCategories.

  • Line 7: The LEFT JOIN is performed with Sales, Employees, and ProductCategories on the columns EID and CategoryID.

  • Line 8: The GROUP BY specifies the EName and CategoryName columns to group the data. ...

Recalling