Easy way to create your Join queries

Contents

• Introduction
• Illustration
• The needed query result
• Northwind's query
• Our query
• Step 1: Tables in the game
• Step 2: Draw the map
• Step 3: Create the queries
• Comparison
• Conclusion

Introduction

Building SQL queries have always been considered the developers' worst nightmare; with all those nested queries and different Joins. In this article, I try to illustrate a somewhat graphical and easy way I use to build nested Join queries, as they are the most common types of queries developers come across. It solved my problems with Join queries, and hopefully, it will solve yours.

Illustration

The easiest way to explain this is by an example, so I chose the very known Northwind database shipped with Microsoft SQL Server. Shortly, we will see: the sample result we want to achieve, how the "Employee Sales by Country" Stored Procedure in the Northwind database achieves it, and how our query will achieve it.

The needed query result

The result we need can wear many human-expression statements. You will see that you can come up with your own statement, but to narrow things down, I choose the following:

"All Orders, shipped between the StartDate and the EndDate (dates are parameters), showing the OrderId, its Shipping Date, its Subtotal, the Employee's (who made the order) First Name, Last Name, and Country".

The query to achieve such a result is a little bit complex; this should never frighten you and stop you from reading to the end of this article. The whole issue here is about how to make complex statements easy to build, so when you see the statements, just hold your breath and bare.

Northwind's query

Northwind database has a ready Stored Procedure that achieves the sample result called "Employee Sales by Country"; my goal of involving it here is to compare our query to it; Complexity and Performance wise.

create procedure "Employee Sales by Country" 
       @Beginning_Date DateTime, 
       @Ending_Date DateTime 
AS
SELECT 
       Employees.Country, 
       Employees.LastName, 
       Employees.FirstName, 
       Orders.ShippedDate, 
       Orders.OrderID, 
       "Order Subtotals".Subtotal AS SaleAmount

FROM Employees 
INNER JOIN 
       (Orders 
       INNER JOIN 
       "Order Subtotals" 
       ON Orders.OrderID = "Order Subtotals".OrderID
       ) 
    
ON Employees.EmployeeID = Orders.EmployeeID

WHERE 
Orders.ShippedDate Between @Beginning_Date And @Ending_Date

GO

But if you notice, this query already uses the View "Order Subtotals", a ready statement. To show how the query really looks like, let us substitute the "Order Subtotals" View with its real query. The following is the result:

SELECT 
       Employees.Country, 
       Employees.LastName, 
       Employees.FirstName, 
       Orders.ShippedDate, 
       Orders.OrderID, 
       OrderSubtotals.Subtotal AS SaleAmount

FROM Employees INNER JOIN 
       (Orders INNER JOIN (

       SELECT 
              "Order Details".OrderID, 
              Sum(CONVERT(money,("Order Details".UnitPrice*
                                  Quantity*(1-Discount)/100))*100) AS Subtotal
       FROM "Order Details"
       GROUP BY "Order Details".OrderID

       ) OrderSubtotals ON Orders.OrderID = OrderSubtotals.OrderID) 
    ON Employees.EmployeeID = Orders.EmployeeID

WHERE Orders.ShippedDate Between @Beginning_Date And @Ending_Date

Executing the above code in the Query Analyzer generates the expected result (you only have to substitute the parameters with their proper values).

Our query

Now, it's our turn to create our query, the goal is to achieve the same result. So, let's follow the steps and see if we will succeed.

Step 1: Tables in the game

First, we specify which tables (along with their needed columns) we need to come up with the wanted result (we should not forget to get the needed Primary keys and the foreign keys by which we will connect to the other tables). I guess this is pretty easy and straightforward.

• Orders:
• OrderId (PK)
• EmployeeId (FK to connect to Employees)
• ShippedDate
• OrderDetails
• OrderId (FK to connect to Orders)
• UnitPrice
• Quantity
• Discount
• SubTotal (a result column calculated by the columns: UnitPrice, Quantity, and Discount)
• Employees
• EmployeeId (PK)
• FirstName
• LastName
• Country

Step 2: Draw the map

After we have determined all the tables and their columns needed, we draw a map of queries that shows us the path to the final result; the best way to describe this is by imagining it as group of creaks joining each other at some point till they form a large river at the end.

We start with the most nucleic queries (that have the least dependency on others), and then we build over it till we have the final result. The map consists of blocks (queries) and lines (to form Joins).

The first nucleic query that comes to my mind is the Orders' Subtotals query; this query is composed of just one table; it makes calculations on some of its fields, adding a new column aliased by "Subtotal":

As you may have noticed, I labeled the query with Q1 since we will keep building on it (adding creeks). The only queries to be given alias names are: the first query (Q1), and all the queries that are a result of a Join; any intrinsic query (other than the first one) won't be given a name.

Now, we need to append to this query the closest one to it; for me, I would join it with Order information (the Shipped Date, and the Employee Id who made the order) through the OrderId field, which resembles:

The results:

Everything is great for now, not much left; we only need to join this with the Employee's information (First Name, Last Name, and Country) through the EmployeeID, that resembles:

The results:

By this, we have the complete query map, and we are ready to start creating the queries.

Step 3: Create the queries

Now, for each joint, we apply the following simple rule till we come up with the final result (refer to the map to know what is intrinsic and what is Qx):

Qx+1=

SELECT 
       intrinsic.fields, 
       Qx.fields 
FROM intrinsic 
JOIN
       ( body of Qx ) as Qx 

ON Qx.[FK] = intrinsic.[PK]

and here we start applying the above rule:

Q1 (base query) =

SELECT 
       "Order Details".OrderID, 
       Sum(CONVERT(money,("Order Details".UnitPrice*Quantity*
                         (1-Discount)/100))*100) AS Subtotal

FROM "Order Details"
GROUP BY "Order Details".OrderID

Q2 =

SELECT 
       Orders.OrderID OrderId, 
       Orders.EmployeeID, 
       Orders.ShippedDate, 
       Q1.Subtotal
FROM
    Orders
JOIN
(
  SELECT "Order Details".OrderID, Sum(CONVERT(money, 
    ("Order Details".UnitPrice*Quantity*(1-Discount)/100))*100) AS Subtotal
  FROM "Order Details"
  GROUP BY "Order Details".OrderID

) AS  Q1 ON Orders.OrderId = Q1.OrderID

Now, we do the same for the final result.

Q3 (final ) =

SELECT 
   Employees.EmployeeID, 
   Employees.Country, 
   Employees.LastName, 
   Employees.FirstName, 
   Q2.OrderId, 
   Q2.ShippedDate, 
   Q2.Subtotal

FROM 
   Employees
JOIN 
(
   SELECT 
          Orders.OrderID OrderId, 
          Orders.EmployeeID, 
          Orders.ShippedDate, 
          Q1.Subtotal 
   FROM 
          Orders 
    JOIN( 

          SELECT 
                 "Order Details".OrderID, 
                 Sum(CONVERT(money,("Order Details".UnitPrice*
                             Quantity*(1-Discount)/100))*100) AS Subtotal
          FROM 
                 "Order Details"
          GROUP BY "Order Details".OrderID

   ) Q1 ON Orders.OrderId = Q1.OrderID

) AS Q2 ON Employees.EmployeeId = Q2.EmployeeId
WHERE Q2.ShippedDate BETWEEN '1/1/1900' AND '1/1/2006'

To this point, we are finished; the query is done.

Comparison

But, just to finalize this; comparing the query we came up with to Northwind's "Employee Sales by Country" Stored Procedure, we will notice:

1. Complexity: our query has more "SELECT" lines within; more to write indeed, but more organized.
2. Performance: I made a quick comparison between the two in the Query Analyzer, and the Execution Plan for the two was as follows:

Northwinds SP "Employee Sales by Country"

Our query

Showing an exact match of the Execution Plan.

Conclusion

We have seen in this article how to build the next Join Queries in just three easy steps:

1. Specify the involved tables and their columns
2. Draw the map
3. Create the queries

Further enhancement to this can be done, and with more thinking...an active developer can create a piece of software that automates this process, making the developers' lives easier.