Retrieving Data from Three Tables without Joins and Subqueries in SQL

=====================================

In this article, we will explore an efficient way to retrieve data from three tables - emp, product, and sales - without using joins and subqueries. The queries we’ll discuss are designed to achieve two specific goals: listing all employees with total sales, fetching the employee with the highest sales, and providing insights into how to accomplish these tasks in a SQL-friendly manner.

Overview of the Tables

To better understand the problem at hand, let’s first review the structure of our three tables:

• emp (Employee Table):

  • id - Unique identifier for each employee.
  • name - Employee name.

• product (Product Table):

  • id - Unique identifier for each product.
  • product_name - Product name.

• sales (Sales Table):

  • id - Unique identifier for each sale record.
  • emp_id - Foreign key referencing the id of the employee who made the sale.
  • product_id - Foreign key referencing the id of the product sold.
  • saleprice - The amount of money earned from the sale.

Solving Problem 1: Retrieve All Employees with Total Sales

To solve this problem without using joins or subqueries, we can leverage grouping and aggregation functions in SQL. Here’s how you could do it:

SELECT emp_id, SUM(salesprice) AS total_sales
FROM sales
GROUP BY emp_id
ORDER BY total_sales DESC;

This query works by grouping the rows of the sales table by their emp_id, calculating the sum of salesprice for each group using the SUM() function, and ordering the results in descending order based on the total_sales.

However, if you want to retrieve all employees with total sales instead of just those with a positive number of sales, things get a bit more complex.

Solution 1: Using GROUPING SETS

SQL Server provides a feature called GROUPING SETS that allows us to group rows differently. This can be used to create an alternative view of the data without using joins or subqueries.

Here’s how you could use GROUPING SETS:

SELECT emp_id, SUM(salesprice) AS total_sales
FROM sales
GROUP BY GROUPING SETS (
    (emp_id),
    (),
    ()
);

In this query, (emp_id) represents the first grouping set. This grouping set groups rows by emp_id. The second empty group (()), and third empty group (()) represent the “all” grouping sets.

The ( emp_id ) expression is used to select only the employees that have sales records associated with them (i.e., the cases where the total sum of sales for a particular employee would be greater than 0). This works around the issue of excluding non-existent data, since there are no rows in sales corresponding to an emp_id that has never made a sale.

Solution 2: Using Window Functions

Another approach is using window functions such as SUM() with an OVER clause. Here’s how you can do it:

SELECT emp_id,
       SUM(salesprice) AS total_sales
FROM sales s1
JOIN (
    SELECT emp_id FROM sales GROUP BY emp_id HAVING COUNT(*) > 0
) s2 ON s1.emp_id = s2.emp_id
GROUP BY emp_id
ORDER BY total_sales DESC;

In this query, we first join the sales table with a subquery that selects all employees who have made at least one sale. This is achieved using the HAVING COUNT(*) > 0 clause within the outer query.

The results are then grouped by emp_id, and sorted in descending order according to their total sales amount.

Solving Problem 2: Retrieve Employee with Highest Sales

To solve this problem, we can follow a similar approach as before. However, since we need only one row, we’ll use the LIMIT clause (available in MySQL).

SELECT emp_id,
       SUM(salesprice) AS total_sales
FROM sales
GROUP BY emp_id
ORDER BY total_sales DESC
LIMIT 1;

This query first groups rows by emp_id, calculates their respective total sales amounts, and then orders these results in descending order. Finally, it selects the row with the highest sales amount using the LIMIT clause.

Note that if there are multiple employees who made the same number of sales, this query would return one of them arbitrarily.

Solution 2: Using Window Functions

Alternatively, we can use window functions to solve this problem as well. Here’s how you could do it:

SELECT emp_id,
       SUM(salesprice) OVER (PARTITION BY emp_id ORDER BY SUM(salesprice)) AS total_sales
FROM sales
GROUP BY emp_id;

In this query, we calculate the sum of salesprice for each group and assign it an alias using the OVER() function. Since we’re grouping by emp_id, the PARTITION BY clause ensures that we get different sums for each employee’s rows. The ORDER BY SUM(salesprice) clause then assigns these sums a natural order based on their values.

Finally, we use this expression to calculate our total sales amounts and return them in descending order, allowing us to select the row with the highest sales amount using the LIMIT clause (available in MySQL).

Last modified on 2023-09-08