In this blog post, we will explore a lesser-known type of join in SQL Server called STRAIGHT_JOIN. Similar to the NATURAL JOIN and INNER JOIN, STRAIGHT_JOIN allows us to combine data from multiple tables. However, it also provides us with the ability to control the order in which the tables are processed.

Let’s start by understanding the basic concept of STRAIGHT_JOIN. According to SQL Server’s documentation, STRAIGHT_JOIN is similar to INNER JOIN, but it forces the left table to be read before the right table. This can be useful in cases where the join optimizer puts the tables in the wrong order.

Let’s consider an example to illustrate the usage of STRAIGHT_JOIN. We have two tables: “items” and “sales”. The “items” table contains information about various items, such as their IDs and descriptions. The “sales” table contains information about sales transactions, including the item ID, sales date, and sales amount.

Here is the SQL code to create and populate the tables:

CREATE TABLE items (
    item_id INT,
    item_description VARCHAR(100)
);

CREATE TABLE sales (
    sales_id INT AUTO_INCREMENT PRIMARY KEY,
    item_id INT,
    sales_date DATETIME,
    sales_amount DECIMAL(12, 2)
);

INSERT INTO items VALUES (1, 'Television');
INSERT INTO items VALUES (2, 'Mobile');
INSERT INTO items VALUES (3, 'Laptop');

INSERT INTO sales (item_id, sales_date, sales_amount) VALUES (1, '2014-01-01', 1200);
INSERT INTO sales (item_id, sales_date, sales_amount) VALUES (2, '2014-01-02', 200);
INSERT INTO sales (item_id, sales_date, sales_amount) VALUES (3, '2014-01-09', 1700);
INSERT INTO sales (item_id, sales_date, sales_amount) VALUES (3, '2014-01-29', 1700);
INSERT INTO sales (item_id, sales_date, sales_amount) VALUES (3, '2014-02-11', 1700);
INSERT INTO sales (item_id, sales_date, sales_amount) VALUES (1, '2014-02-16', 1200);
INSERT INTO sales (item_id, sales_date, sales_amount) VALUES (2, '2014-02-16', 200);
INSERT INTO sales (item_id, sales_date, sales_amount) VALUES (2, '2014-02-20', 200);
INSERT INTO sales (item_id, sales_date, sales_amount) VALUES (2, '2014-02-20', 200);
INSERT INTO sales (item_id, sales_date, sales_amount) VALUES (2, '2014-02-22', 200);
INSERT INTO sales (item_id, sales_date, sales_amount) VALUES (3, '2014-02-24', 1700);
INSERT INTO sales (item_id, sales_date, sales_amount) VALUES (1, '2014-02-24', 1200);

Now, let’s explore two methods of using STRAIGHT_JOIN:

Method 1: Join tables using STRAIGHT_JOIN

SELECT t1.item_description, t2.sales_id, t2.sales_date, t2.sales_amount
FROM sales AS t2
STRAIGHT_JOIN items AS t1 ON t1.item_id = t2.item_id;

Method 2: Use INNER JOIN and STRAIGHT_JOIN in the SELECT clause

SELECT STRAIGHT_JOIN t1.item_description, t2.sales_id, t2.sales_date, t2.sales_amount
FROM sales AS t2
INNER JOIN items AS t1 ON t1.item_id = t2.item_id;

Both of these queries will produce the same result, which includes the item description, sales ID, sales date, and sales amount.

However, the real test is when we execute the STRAIGHT_JOIN syntax with the EXPLAIN command before the SELECT statement. This allows us to observe the order in which the tables are joined.

Method STRAIGHT JOIN (with EXPLAIN)

EXPLAIN SELECT STRAIGHT_JOIN t1.item_description, t2.sales_id, t2.sales_date, t2.sales_amount
FROM sales AS t2
INNER JOIN items AS t1 ON t1.item_id = t2.item_id;

By examining the result, we can observe the order of the tables used in the join. In this case, the “sales” table (aliased as t2) is first used in the result set.

Method INNER JOIN (without STRAIGHT JOIN) (with EXPLAIN)

EXPLAIN SELECT t1.item_description, t2.sales_id, t2.sales_date, t2.sales_amount
FROM sales AS t2
INNER JOIN items AS t1 ON t1.item_id = t2.item_id;

By comparing the result with the previous query, we can see that the “sales” table (aliased as t2) is second used in the result set. This indicates that SQL Server’s internal optimizer has re-ordered the table join for maximum performance.

In conclusion, STRAIGHT_JOIN is a powerful tool in SQL Server that allows us to control the order in which tables are processed during a join operation. By using STRAIGHT_JOIN, we can optimize our queries and potentially improve performance.