Logical-joins-TSQL.sql

/*****************************************************************************
Copyright (c) 2017 SQL Workbooks LLC
MIT License, http://www.opensource.org/licenses/mit-license.php
Get WideWorldImporters-Full.bak from Microsoft at:
https://github.com/Microsoft/sql-server-samples/releases/tag/wide-world-importers-v1.0
******************************************************************************/

USE master;
GO

IF DB_ID('WideWorldImporters') IS NOT NULL
BEGIN
    ALTER DATABASE WideWorldImporters SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
END
GO
RESTORE DATABASE WideWorldImporters FROM DISK=
	'S:\MSSQL\Backup\WideWorldImporters-Full.bak'  
	WITH REPLACE,
	MOVE 'WWI_Primary' to 'S:\MSSQL\Data\WideWorldImporters.mdf',
	MOVE 'WWI_UserData' to 'S:\MSSQL\Data\WideWorldImporters_UserData.ndf',
	MOVE 'WWI_Log' to 'S:\MSSQL\Data\WideWorldImporters.ldf',
	MOVE 'WWI_InMemory_Data_1' to 'S:\MSSQL\Data\WideWorldImporters_InMemory_Data_1';
GO


USE WideWorldImporters;
GO


/* Make the data a little more interesting */
ALTER TABLE [Sales].[Invoices] DROP CONSTRAINT [FK_Sales_Invoices_OrderID_Sales_Orders]
GO
ALTER TABLE [Sales].[Orders] DROP CONSTRAINT [FK_Sales_Orders_BackorderOrderID_Sales_Orders]
GO
ALTER TABLE [Sales].[CustomerTransactions] DROP CONSTRAINT [FK_Sales_CustomerTransactions_InvoiceID_Sales_Invoices]
GO
ALTER TABLE [Warehouse].[StockItemTransactions] DROP CONSTRAINT [FK_Warehouse_StockItemTransactions_InvoiceID_Sales_Invoices]
GO

DELETE FROM Sales.OrderLines WHERE OrderID >= 70000
GO
DELETE FROM Sales.Orders WHERE OrderID >= 70000
GO
DELETE FROM Sales.InvoiceLines WHERE InvoiceID <= 100
GO
DELETE FROM Sales.Invoices WHERE InvoiceID <= 100
GO


/* Inner joins */

--67,094
SELECT COUNT(*)
FROM Sales.Invoices as inv
JOIN Sales.Orders as ord on
	inv.OrderID = ord.OrderID;
GO

--67,094
SELECT COUNT(*)
FROM Sales.Invoices as inv, 
	Sales.Orders as ord
WHERE inv.OrderID = ord.OrderID;
GO


/* Outer joins */
--70,410
SELECT COUNT(*)
FROM Sales.Invoices as inv
LEFT JOIN Sales.Orders as ord on
	inv.OrderID = ord.OrderID;
GO

--69,999
SELECT COUNT(*)
FROM Sales.Invoices as inv
RIGHT JOIN Sales.Orders as ord on
	inv.OrderID = ord.OrderID;
GO



/* Full join */

--73,315
SELECT COUNT(*)
FROM Sales.Invoices as inv
FULL JOIN Sales.Orders as ord on
	inv.OrderID = ord.OrderID;
GO


/* Cross join */
--4,928,629,590   ( SELECT 70410 * 69999. )
SELECT COUNT_BIG(*)
FROM Sales.Invoices as inv
CROSS JOIN Sales.Orders as ord; 
GO


/* Cross apply */
--67,094
SELECT COUNT(*)
FROM Sales.Invoices as inv
CROSS APPLY 
	(SELECT 1/0 as y 
	FROM Sales.Orders AS ord 
	WHERE inv.OrderID = ord.OrderID) as x;
GO

/* Outer apply */
--70,410
SELECT COUNT(*)
FROM Sales.Invoices as inv
OUTER APPLY 
	(SELECT 1/0 as y 
	FROM Sales.Orders AS ord 
	WHERE inv.OrderID = ord.OrderID) as x;
GO