In this post I’ll look at some SQL joins using Tableau’s Superstore database as inspiration. This is not the Superstore database. I’ll have only a few tables and only a couple of rows. Why? This post is just to illustrate that the original data is likely to be in a different format that what we get with Tableau. The Tableau data is very wide. Wide means that there are many columns of data.
In a non-production server, run this SQL script to create the tables and insert the data. To copy the script, click on the view source button in the top right corner of the script below. Select all the text and copy it. Paste it to a new query in SQL Server’s Management Studio.
Create the Example Database in SQL Server
CREATE DATABASE [Superstore2] GO USE [Superstore2] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Category]( [Id] [int] NOT NULL, [CatName] [nvarchar](50) NULL, CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[OrderDetails] Script Date: 10/1/2023 4:00:41 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[OrderDetails]( [Id] [int] NOT NULL, [OrderId] [int] NULL, [ProductId] [nvarchar](255) NULL, [Sales] [float] NULL, [Quantity] [float] NULL, [Discount] [float] NULL, [Profit] [float] NULL CONSTRAINT [PK_OrderDetails] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[Orders] Script Date: 10/1/2023 4:00:41 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Orders]( [Id] [int] NOT NULL, [OrderDate] [date] NULL, [ShipDate] [date] NULL, [ShipMode] [nvarchar](255) NULL, [Region] [nvarchar](255) NULL, [PostalCode] [float] NULL, [State] [nvarchar](255) NULL, [City] [nvarchar](255) NULL, [Country] [nvarchar](255) NULL, [Segment] [nvarchar](255) NULL, [CustomerName] [nvarchar](255) NULL, [CustomerID] [nvarchar](255) NULL CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[Product] Script Date: 10/1/2023 4:00:41 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Product]( [ProductId] [nvarchar](255) NOT NULL, [ProductName] [nvarchar](255) NULL, [CategoryId] [int] NULL, [SubcategoryId] [int] NULL CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED ( [ProductId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[Subcategory] Script Date: 10/1/2023 4:00:41 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Subcategory]( [Id] [int] NOT NULL, [CatId] [int] NULL, [SubCatName] [nvarchar](100) NULL, CONSTRAINT [PK_Subcategory] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO -- *************************************************************************************************************************** INSERT [dbo].[Category] ([Id], [CatName]) VALUES (1, N'Furniture') INSERT [dbo].[Category] ([Id], [CatName]) VALUES (2, N'Technology') GO -- INSERT [dbo].[Orders] ([Id], [OrderDate], [ShipDate], [ShipMode], [Region], [PostalCode], [State], [City], [Country], [Segment], [CustomerName], [CustomerID]) VALUES (1, CAST(N'2023-03-05' AS Date), CAST(N'2023-03-17' AS Date), N'Slow', N'West', 12345, N'Califortnia', N'LA', N'United States', N'Consumer', N'Jack', N'2922') INSERT [dbo].[Orders] ([Id], [OrderDate], [ShipDate], [ShipMode], [Region], [PostalCode], [State], [City], [Country], [Segment], [CustomerName], [CustomerID]) VALUES (2, CAST(N'2023-03-06' AS Date), CAST(N'2023-03-08' AS Date), N'Express', N'East', 24689, N'California', N'San Jose', N'United States', N'Consumer', N'Jill', N'3401') GO -- Fine INSERT [dbo].[Product] ([ProductId], [ProductName], [CategoryId], [SubcategoryId]) VALUES (N'F10001756', N'Eldon Expressions Shelf Wood', 1, 1) INSERT [dbo].[Product] ([ProductId], [ProductName], [CategoryId], [SubcategoryId]) VALUES (N'F10000223', N'Smith Chair Pine', 1, 2) INSERT [dbo].[Product] ([ProductId], [ProductName], [CategoryId], [SubcategoryId]) VALUES (N'F10007710', N'Jackson Model 7 Book Oak', 1, 1) INSERT [dbo].[Product] ([ProductId], [ProductName], [CategoryId], [SubcategoryId]) VALUES (N'T10002217', N'Flip Phone H455', 2, 3) INSERT [dbo].[Product] ([ProductId], [ProductName], [CategoryId], [SubcategoryId]) VALUES (N'T10001218', N'Mobile Phone P997', 2, 3) INSERT [dbo].[Product] ([ProductId], [ProductName], [CategoryId], [SubcategoryId]) VALUES (N'T10003308', N'Accessory Protector', 2, 4) GO -- INSERT [dbo].[Subcategory] ([Id], [CatId], [SubCatName]) VALUES (1, 1, N'Bookcases') INSERT [dbo].[Subcategory] ([Id], [CatId], [SubCatName]) VALUES (2, 1, N'Chairs') INSERT [dbo].[Subcategory] ([Id], [CatId], [SubCatName]) VALUES (3, 2, N'Phones') INSERT [dbo].[Subcategory] ([Id], [CatId], [SubCatName]) VALUES (4, 2, N'Accessories') GO -- INSERT [dbo].[OrderDetails] ([Id], [OrderID], [ProductId], [Sales], [Quantity], [Discount], [Profit]) VALUES (1,1, N'F10001756', 60, 1, 0, 7.88) INSERT [dbo].[OrderDetails] ([Id], [OrderID], [ProductId], [Sales], [Quantity], [Discount], [Profit]) VALUES (2,1, N'F10007710', 230, 1, 0, 49) INSERT [dbo].[OrderDetails] ([Id], [OrderID], [ProductId], [Sales], [Quantity], [Discount], [Profit]) VALUES (3,2, N'T10001218', 150, 1, 0, 39) GO
I suggest copying the query below and creating a new view. In SQL Server, paste the text at the bottom of the pane.
SELECT dbo.Orders.Id AS OrderId, dbo.Orders.OrderDate, dbo.OrderDetails.Sales, dbo.OrderDetails.ProductId, dbo.Product.ProductName, dbo.Category.CatName, dbo.Subcategory.SubCatName
FROM dbo.Product INNER JOIN
dbo.OrderDetails ON dbo.Product.ProductId = dbo.OrderDetails.ProductId INNER JOIN
dbo.Category ON dbo.Product.CategoryId = dbo.Category.Id INNER JOIN
dbo.Subcategory ON dbo.Product.SubcategoryId = dbo.Subcategory.Id LEFT OUTER JOIN
dbo.Orders ON dbo.OrderDetails.OrderId = dbo.Orders.Id
Below is a annotated screenshot from SQL Server.
An inner join is used to find related data in two tables. For example, suppose you need to retrieve data about a product and its category or subcategory. You can use an inner join. On the other hand, an outer join is used to retrieve all rows from one table, and any corresponding rows from a related table. In cases where a row in the outer table has no corresponding rows in the related table, NULL values are returned for the related table fields.
The Orders Table
The Orders Detail Table
