SQL DBA Survival Guide

T-SQL Code - Part 3

This chapter includes many activities that demonstrate the functionality of the topics.

Note: some of the activities have dependencies on prior activities being performed for the objects to exist.

 

Conditional Logic

IF

The IF condition can only be true or false. If something evaluates to true, otherwise it is false.

A common place to see the IF condition is in the built-in DROP Table script generated by SSMS

--Example
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyTable]') AND type in (N'U'))
DROP TABLE [dbo].[MyTable]

The above script is not as scary as it may look.

The essential part if the IF which is evaluates conditional logic.

The logic is IF true, THEN do something.

\Run these scripts, then change the @MyName value to ‘Jane’ and notice the logic behavior.

-- 0401-01-05 Demonstration Code ================
DECLARE @MyName [varchar](50)
SET @MyName = 'Sam'
IF @MyName = 'Sam' SELECT 'Sam I am'

-- 0401-01-10 Demonstration Code ================
-- Use multiple IFs
DECLARE @MyName [varchar](50)
SET @MyName = 'Sam'
IF @MyName = 'Sam'
SELECT 'Sam I am'
IF @MyName <> 'Sam'
SELECT 'I am NOT Sam'

IF NOT

The IF NOT condition can only be true or false. If something evaluates to false, otherwise it is true.

SET @MyName = 'Jane'
IF NOT @MyName = 'Sam' SELECT 'I am NOT Sam'

-- 0401-02-05 Demonstration Code ================
-- Use multiple IF NOTs
DECLARE @MyName [varchar](50)
SET @MyName = 'Sam'
IF NOT @MyName = 'Sam'
SELECT 'Sam I am'
IF NOT @MyName <> 'Sam'
SELECT 'I am NOT Sam'

IF ELSE

Controls the flow of execution in SQL Server

The scripts below do the same thing., observe how they work when the @Name value is changed.

-- 0401-03-05 Demonstration Code ================
-- Use multiple IFs
DECLARE @MyName [varchar](50)
SET @MyName = 'Sam'
IF @MyName = 'Sam'
PRINT 'Sam I am'
IF @MyName <> 'Sam'
PRINT 'I am NOT Sam'

-- 0401-03-10 Demonstration Code ================
-- Using IF ELSE
DECLARE @MyName [varchar](50)
SET @MyName = 'Sam'
IF @MyName = 'Sam'
PRINT 'Sam I am'
ELSE
PRINT 'I am NOT Sam'

-- 0401-03-15 Demonstration Code ================
-- Using IF ELSE
DECLARE @MyName [varchar](50)
SET @MyName = 'Jane'
IF @MyName = 'Sam'
PRINT 'Sam I am'
ELSE
PRINT 'I am NOT Sam'

BEGIN / END

The previous scripts work well when the work to be done is a single statement.

But when more than one statement is needed, we must wrap the statements in a BEGIN / END.

Observe the result of this script, and compare it after changing the @MyName value.

-- 0401-04-05 Demonstration Code ================
-- Incorrect script that appears to work, but only works when the condition is true
DECLARE @MyName [varchar](50)
SET @MyName = 'Sam'
IF @MyName = 'Sam'
PRINT 'Sam I am'
PRINT 'I should only appear if the condition is true'

Since the IF condition is true, the results look as expected.

BegonEnd01

-- 0401-04-10 Demonstration Code ================
-- Change @MyName to 'Jane' to create a false condition
-- This script exhibits incorrect behavior when the condition is false
DECLARE @MyName [varchar](50)
SET @MyName = 'Jane'
IF @MyName = 'Sam'
SELECT 'Sam I am'
SELECT 'I should only appear if the condition is true'

What happened? The first statement did not execute because the condition is not true. The second statement did execute even though the condition is not true.

The way to code for the logic

-- 0401-04-10 Demonstration Code ================
-- No message is generated, correct behavior
DECLARE @MyName [varchar](50)
SET @MyName = 'Jane'
IF @MyName = 'Sam'
BEGIN
PRINT 'Sam I am'
PRINT 'I should only appear if the condition is true'
END

-- 0401-04-15 Demonstration Code ================
-- 3 Messages generated, correct behavior
DECLARE @MyName [varchar](50)
SET @MyName = 'Sam'
IF @MyName = 'Sam'
BEGIN
PRINT 'Sam I am'
PRINT 'I should only appear if the condition is true'
END
PRINT 'Always display me'

BegonEnd02

-- 0401-04-20 Demonstration Code ================
-- Change @MyName to 'Jane' to create a false condition
-- 1 Message generated, correct behavior
DECLARE @MyName [varchar](50)
SET @MyName = 'Jane'
IF @MyName = 'Sam'
BEGIN
PRINT 'Sam I am'
PRINT 'I should only appear if the condition is true'
END
PRINT 'Always display me'

BegonEnd03

CASE

The CASE expression evaluates conditions and returns a value when the first condition is met (like an if-then-else statement)

CASE also uses WHEN, and THEN, it can also use ELSE

-- 0401-05-05 Demonstration Code ================
DECLARE @MyNumber [int]
SET @MyNumber = 50

SELECT
CASE
WHEN @MyNumber > 50 THEN 'The number is greater than 50'
WHEN @MyNumber = 50 THEN 'The number is 50'
ELSE 'The number is less than 50'
END

 

Loops

WHILE Loop

While iterates through a loop until a condition is met.

-- 0402-01-05 Demonstration Code ================
DECLARE @Counter int
SET @Counter=1
WHILE (@Counter <= 10)
BEGIN
PRINT 'The counter value is = ' + CONVERT(VARCHAR,@Counter)
SET @Counter = @Counter + 1
END

CURSOR Loop

A Cursor is a loop.

Cursors uses FETCH and WHILE

Failing code

-- 0402-02-05 Demonstration Code ================
-- Fails because the @CurrentID is an INT data types being added to a string
/* Set up variables to hold the current record we are working on */
DECLARE @CurrentID INT, @CurrentString VARCHAR(100);

-- Load records into the cursor
DECLARE cursor_results CURSOR FOR
SELECT [ID], [Name]
FROM dbo.MyOtherTable;

OPEN cursor_results
FETCH NEXT FROM cursor_results into @CurrentID, @CurrentString
WHILE @@FETCH_STATUS = 0
BEGIN

/* Do something with your ID and string */
PRINT 'ID = ' + @CurrentID + ' String = ' + @CurrentString

FETCH NEXT FROM cursor_results into @CurrentID, @CurrentString
END

/* Clean up our work */
CLOSE cursor_results;
DEALLOCATE cursor_results;

Working code

-- 0402-02-10 Demonstration Code ================
-- Works
/* Set up variables to hold the current record we are working on */
DECLARE @CurrentID INT, @CurrentString VARCHAR(100), @Msg varchar(50);

DECLARE cursor_results CURSOR FOR
SELECT [ID], [Name]
FROM dbo.MyOtherTable;

OPEN cursor_results
FETCH NEXT FROM cursor_results into @CurrentID, @CurrentString
WHILE @@FETCH_STATUS = 0
BEGIN

/* Do something with your ID and string */
SET @Msg = 'ID = ' + CAST(@CurrentID AS varchar(50)) + ' String = ' + @CurrentString
PRINT @Msg

FETCH NEXT FROM cursor_results into @CurrentID, @CurrentString
END

/* Clean up our work */
CLOSE cursor_results;
DEALLOCATE cursor_results;

 

TRY / CATCH

Syntax

BEGIN TRY
-- Write statements here that may cause exception
END TRY
BEGIN CATCH
-- Write statements here to handle exception
END CATCH

Failing script


-- Code throws an error
SELECT 2/0

Working script

-- 0403-00-05 Demonstration Code ================
-- TRY creates an error, CATCH gracefully handles the error
BEGIN TRY
SELECT 2/0
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS [Error Message]
,ERROR_LINE() AS [Error Line]
,ERROR_NUMBER() AS [Error Number]
,ERROR_SEVERITY() AS [Error Severity]
,ERROR_STATE() AS [Error State]
END CATCH

 

Built-in Functions

This section will focus on several of the built-in TSQL functions available.

Functions are used to return a value. Many functions are available in TSQL, these are a few of the more common functions.

GETDATE

The GetDate() function returns the current data and time.

SELECT GETDATE()

DATEADD

The DateAdd() function is used to add or subtract days from a date value

Syntax

-- 0404-02-05 Demonstration Code ================
DATEADD(interval, number, date)
-- DateAdd Add 1 year to a date
SELECT DATEADD(year, 1, '2024/08/25') AS DateAdd;

-- 0404-02-10 Demonstration Code ================
-- DateAdd Add 3 months to today
SELECT DATEADD(month, 3, GETDATE()) AS DateAdd;

-- 0404-02-15 Demonstration Code ================
-- DataAdd Subtract 25 days from today
SELECT DATEADD(day, -25, GETDATE()) AS DateAdd;

DATEDIFF

The DateDiff() function returns the number of days between two date values.

Syntax

DATEDIFF(interval, date1, date2)

-- 0404-03-05 Demonstration Code ================
-- DateDiff
SELECT DATEDIFF(year, '2017/08/25', '2011/08/25') AS DateDiff

SUBSTRING

The SubString() function returns a part of the string passed to it.

Syntax

SUBSTRING(string, start, length)

-- 0404-04-05 Demonstration Code ================
-- SubString Extract 1st 5 characters from a string
SELECT SUBSTRING('My String to test', 1, 5) AS ExtractString;

-- 0404-04-10 Demonstration Code ================
-- SubString Extract 5 characters from a string starting with the 4th character
SELECT SUBSTRING('My String to test', 4, 5) AS ExtractString;

REPLACE

The Replace() function replaces all occurrences of a substring within a string, with a new substring.

Note: The search is case-insensitive.

Syntax

REPLACE ( string_expression , string_pattern , string_replacement )


The following example replaces the string cde in abcdefghi with xxx.

-- 0404-05-05 Demonstration Code ===============
-- Replace CDE
SELECT REPLACE('abcdefghicde','cde','xxx')

-- 0404-05-10 Demonstration Code ===============
-- Replace ABC
SELECT REPLACE('ABC ABC ABC', 'a', 'c')

-- 0404-05-15 Demonstration Code ===============
-- Replace GetDate
REPLACE(CONVERT(VARCHAR (30),GETDATE (),121), ':', '')

Replace can be used in a similar manner to this.

-- 0404-05-20 Demonstration Code ===============
-- Replace multiple calls
DECLARE @Filename nvarchar(128)
SET @Filename = '\This is not a very, <good> filename.txt'
PRINT @Filename
PRINT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@Filename ,'<','_'),'>','_'), '\','_'), ' ','_'),',','_')

Additional Reading:
https://msdn.microsoft.com/en-us/library/ms186862.aspx

COALESCE

The COALESCE() function returns the first non-null value in a list. If all the values in the list are NULL, then the function returns null.

Works for SQL Server 2008 and newer.

This function is useful when combining the values from several columns into one.

For example, a table called users contains values of users' work_email and personal_email.

Using the COALESCE() function, we can create a column called email, which shows the user's work_email if it is not null. Otherwise, it shows personal_email.

Syntax

COALESCE(value_1, value_2, ...., value_n)

DB_ID

Many scripts require the ID of a database instead of the Name of the database. The DB_ID function can be used to get the database ID.

Syntax

DB_ID('MyDatabase')

OBJECT_ID

Many scripts require the ID of a table instead of the Name of the table. The OBJECT_ID function can be used to get the table ID.

Syntax

OBJECT_ID(<MyTableName>)

ROWCOUNT

The RowCount() function follows a SELECT statement, this function returns the number of rows returned by the SELECT statement.

@@ROWCOUNT

Following a SELECT statement, this function returns the number of rows returned by the SELECT statement.

Following an INSERT, UPDATE, or DELETE statement, this function returns the number of rows affected by the data modification statement.

SELECT * FROM MyTable
SELECT @@ROWCOUNT

ROWCOUNT_BIG ( )

If the number of rows is more than 2 billion, use ROWCOUNT_BIG.

returns a bigint

SELECT * FROM MyTable
SELECT ROWCOUNT_BIG()

-- https://www.codeproject.com/Tips/58478/Record-Count-of-Tables-in-SQL-Server
SELECT
T.TABLE_NAME AS [TABLE NAME], MAX(I.ROWS) AS [RECORD COUNT]
FROM SYSINDEXES I, INFORMATION_SCHEMA.TABLES T
WHERE T.TABLE_NAME = OBJECT_NAME(I.ID)
AND T.TABLE_TYPE = 'BASE TABLE'
GROUP BY T.TABLE_SCHEMA, T.TABLE_NAME

 

Dynamic Scripts

N

N is used to designate that the following text is the be treated as UNICODE (international characters) vs ASCII. It does not hurt to use this syntax; in some cases, it is necessary (demonstrated below). Essentially, this defines the text as nvarchar instead of varchar.

Syntax

N’some text here’

Parameters

Parameters are variables that are defined in the definition of a method. They are assigned the values which are passed as arguments when the method is called, elsewhere in the code.

EXEC

Execute a script without parameters.

Syntax

EXEC (script2run)

-- 0406-01-05 Demonstration Code ================
-- Dynamic Script Variables
-- EXEC
DECLARE @SqlStatment AS NVARCHAR(1000)
DECLARE @ColNames AS NVARCHAR(100)

SET @ColNames = N'[ID], [Name], [Description]';
SET @SqlStatment = 'SELECT ' + @ColNames + ' FROM [MyIdentityTable]'

EXEC (@SqlStatment)

sp_executesql

Used to execute a SQL script with parameters

Syntax

EXECUTE sp_executesql script2run, @optional paramaeters

Code Without a parameter

-- 0406-02-05 Demonstration Code ================
-- Dynamic Script Variables
-- sp_executesql Without parameters
DECLARE @SqlStatment AS NVARCHAR(1000)
DECLARE @ColNames AS NVARCHAR(100)

SET @ColNames = N'[ID], [Name], [Description]'
SET @SqlStatment = 'SELECT ' + @ColNames + ' FROM [MyIdentityTable]'

EXECUTE sp_executesql @SqlStatment

Code With a parameter
-- 0406-02-10 Demonstration Code ================
-- Dynamic Script Variables
-- sp_executesql With parameter
DECLARE @SqlStatment AS NVARCHAR(1000)
DECLARE @ColNames AS NVARCHAR(100)

SET @ColNames = N'[ID], [Name], [Description]'
SET @SqlStatment = 'SELECT ' + @ColNames + ' FROM [MyIdentityTable] WHERE ID=@IDParm'

EXECUTE sp_executesql @SqlStatment, N'@IDParm int',@IDParm=2

In this script, the parameters for sp_executesql require parameters be the unicode data type.

-- 0406-02-15 Demonstration Code ================
-- Dynamic Script Variables
-- sp_executesql Fail
-- FAILS: The method requires a string parameter designated by N (Unicode data type)
DECLARE @SqlStatment AS NVARCHAR(1000)
DECLARE @ColNames AS NVARCHAR(100)

SET @ColNames = N'[ID], [Name], [Description]'
SET @SqlStatment = 'SELECT ' + @ColNames + ' FROM [MyIdentityTable] WHERE ID=@IDParm'

EXECUTE sp_executesql @SqlStatment, '@IDParm int',@IDParm=2

sp_executesql

Dynamic Comments

When executing SQL code in environments such as PowerShell, the code is treated as a single line of code. This is a problem if in-line comments are used.

Concatenating Dynamic Code from multiple variables

-- 0406-03-05 Demonstration Code ================
-- Works as expected
DECLARE @A varchar(10)
DECLARE @B varchar(10)
DECLARE @C varchar(20)

SET @A = 'Here' -- First Part
SET @B = ' and There' -- Second Part
SET @C = @A + @B

PRINT 'Combined = ' + @C

Concatenating Dynamic Code as 1 variable

The double dash comment marker will make all the code after the first occurrence part of the comment.

-- 0406-03-10 Demonstration Code ================
--Fails because the comment is not terminated
DECLARE @A nvarchar(1000)
SET @A = N'SELECT * -- Select Part' +
' FROM sys.databases -- From Part '
PRINT 'Combined = ' + @A
EXECUTE sp_executesql @A

DynamicComments01

-- 0406-03-15 Demonstration Code ================
-- Works as expected because the comment is terminated
DECLARE @A nvarchar(1000)
SET @A = N'SELECT * /* Select Part */' +
' FROM sys.databases /* From Part */'
PRINT 'Combined = ' + @A
EXECUTE sp_executesql @A

DynamicComments02

DynamicComments03

 

Joins

A SQL join clause combines columns from one or more tables in a relational database. It creates a result set that can be saved as a table or used as it is.

Typically, each table in a database stores a specific type of information. So, often there are hundreds of tables related to each other in a database. This implies the need for joins. You can join different tables by their common columns using the JOIN keyword.

Joins are mostly used to combine column data from 2 or more tables into a single result set.

These tables will be used to illustrate the joint types in the following demonstrations. Execute this script to create the tables and data.

-- 0407-00-05 Demonstration Code ================
USE [MyDatabase];
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO

-- Create Facebook table
CREATE TABLE [dbo].[Facebook](
[Name] [varchar](50) NULL,
[Friends] [int] NULL
) ON [PRIMARY]
GO

INSERT INTO Facebook ([name], [Friends])
VALUES ('Jane',75);

INSERT INTO Facebook ([name], [Friends])
VALUES ('Alex',290);

INSERT INTO Facebook ([name], [Friends])
VALUES ('Alice',170);
GO

-- Create LinkedIn table
CREATE TABLE [dbo].[LinkedIn](
[Name] [varchar](50) NULL,
[Friends] [int] NULL
) ON [PRIMARY]
GO

-- Add data to LinkedIn
INSERT INTO LinkedIn ([name], [Friends])
VALUES ('John',200);

INSERT INTO LinkedIn ([name], [Friends])
VALUES ('Jane',300);

INSERT INTO LinkedIn ([name], [Friends])
VALUES ('Alex',150);

INSERT INTO LinkedIn ([name], [Friends])
VALUES ('Mary',240);

-- Add data to LinkedIn
INSERT INTO Facebook ([name], [Friends])
VALUES ('John',130);
GO

Additional reading:
https://en.wikipedia.org/wiki/Join_(SQL)
https://www.atlassian.com/data/sql/sql-join-types-explained-visually

Aliases

Aliases can be used to make the output more readable.

This query will return the value with a column name '(no column name)'.

SELECT @@SPID

This query will return the value with a column name 'My Current Session ID'.

SELECT @@SPID AS [My Current Session ID]

Aliases can also be used to make some queries more readable, aliases may be used as a shorthand method to reference tables. This is done by using the 'AS' statement.

Longhand query version

-- 0407-01-05 Demonstration Code ================
-- Longhand query versionSELECT sys.database_principals.principal_id
,sys.database_principals.name
,sys.database_principals.type_desc
,sys.database_principals.authentication_type_desc
,sys.database_permissions.state_desc
,sys.database_permissions.permission_name
FROM sys.database_principals
INNER JOIN sys.database_permissions
ON sys.database_permissions.grantee_principal_id = sys.database_principals.principal_id

The above query can be written as shown below.

  • sys.database_principals is aliased as pr
  • sys.database_permissions is aliased as pe

Shortcut query version

-- 0407-01-10 Demonstration Code ================
-- Shortcut query version using table aliases
SELECT pr.principal_id
,pr.name
,pr.type_desc
,pr.authentication_type_desc
,pe.state_desc
,pe.permission_name
FROM sys.database_principals AS pr
INNER JOIN sys.database_permissions AS pe
ON pe.grantee_principal_id = pr.principal_id

Inner Join

An Inner Join returns rows when there is a match in both tables. The shortcut for an Inner Join is simply Join.

InnerJoin01

Syntax:

SELECT table1.field1, table2.field2
FROM table1
JOIN table2
ON table2.commonfield = table1.commonfield

TIP: When using Joins, the ON acts like a WHERE clause.

Example:

-- 0407-01-10 Demonstration Code ================
-- Shortcut query version using table aliases
SELECT pr.principal_id
,pr.name
,pr.type_desc
,pr.authentication_type_desc
,pe.state_desc
,pe.permission_name
FROM sys.database_principals AS pr
INNER JOIN sys.database_permissions AS pe
ON pe.grantee_principal_id = pr.principal_id

Broken example

Since Name and Friends are column names is bother tables, SQL does not know which column is being referred to. The the columns must be prefixed with the table name.
-- 0407-02-05 Demonstration Code ================
-- Broken example
-- Since Name and Friends are column names is bother tables,
-- SQL does not know which column is being referred to
SELECT [Name], [Friends], [Name], [Friends]
FROM [Facebook]
JOIN [LinkedIn]
ON [LinkedIn].[Name] = [Facebook].[Name]

InnerJoin02

Example:

-- 0407-02-10 Demonstration Code ================
-- Longhand query version
SELECT [Facebook].[Name], [Facebook].[Friends], [LinkedIn].[Name], [LinkedIn].[Friends]
FROM [Facebook]
JOIN [LinkedIn]
ON [LinkedIn].[Name] = [Facebook].[Name]

Can be written as

  • facebook is aliased as F
  • linkedin is aliased as L

-- 0407-02-15 Demonstration Code ================
-- Shortcut query version using table aliases
SELECT F.[Name], F.[Friends], L.[Name], L.[Friends]
FROM [Facebook] F
JOIN [LinkedIn] L
ON L.[Name] = F.[Name]

InnerJoin03

Even Better

Add aliases for the column names to identify which table the column is from.

-- 0407-02-20 Demonstration Code ================
-- Using column aliases
SELECT F.[Name] AS F_Name, F.[Friends] AS F_Friends, L.[Name] AS L_Name, L.[Friends] AS L_Friends
FROM [Facebook] F
JOIN [LinkedIn] L
ON L.[Name] = F.[Name]

InnerJoin04

Left Join

This is the second most common type of JOIN in SQL. Left refers to the first table, or the table you will be joining to. So in this case it would be the facebook table since it comes before linkedin table in the query.

LeftJoin01

A Left Join returns all rows from the left table, even if there are no matches in the right table.

-- 0407-03-05 Demonstration Code ================
SELECT *
FROM [Facebook] f
LEFT JOIN [LinkedIn] l
ON f.[Name] = l.[Name]

LeftJoin02

Right Join

A Right Join returns all rows from the right table, even if there are no matches in the left table.

RightJoin01

-- 0407-04-05 Demonstration Code ================
SELECT *
FROM [Facebook] f
RIGHT JOIN [LinkedIn] l
ON f.[Name] = l.[Name]

RightJoin02

Full Join

A Full Join returns rows when there is a match in one of the tables.

An Outer Join creates a result set with all rows in all tables.

A Full Join returns rows when there is a match in one of the tables.

AKA: FULL JOIN and FULL OUTER JOIN

FullJoin01

-- 0407-05-05 Demonstration Code ================
SELECT *
FROM [Facebook] f
FULL OUTER JOIN [LinkedIn] l
ON f.[Name] = l.[Name]

FullJoin02

Union

The SQL UNION clause/operator is used to combine the results of two or more SELECT statements without returning any duplicate rows.

To use UNION, each SELECT must have the same number of columns selected, the same number of column expressions, the same data type, and have them in the same order, but they do not have to be the same length.

A Union Join stacks tables on top of each other resulting in new rows.

Union01

Syntax:

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
UNION
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

-- 0407-06-05 Demonstration Code ================
SELECT *
FROM [Facebook]
UNION
SELECT *
FROM [LinkedIn]

Union02

Cross Join

A Cross Join returns the Cartesian product of the sets of records from the two or more joined tables. Also known as Cartesian Join.

A Cross Join results in a table with all possible combinations of your tables’ rows together. This can result in enormous tables and should be used with caution.

CrossJoin01

-- 0407-07-05 Demonstration Code ================
SELECT *
FROM [Facebook] f
CROSS JOIN [LinkedIn] l

CrossJoin02

Self Join

A Self Join is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.

Most JOINs link two or more tables with each other to present their data together, a self join links a table to itself. This is usually done by joining a table to itself just once within a SQL query, but it is possible to do so multiple times within the same query.

The self join, joins a table to itself. To use a self join, the table must contain a column (call it X) that acts as the primary key and a different column (call it Y) that stores values that can be matched up with the values in Column X. The values of Columns X and Y do not have to be the same for any given row, and the value in Column Y may even be null.

Employee Examples

Create the table and data for the demonstration

-- 0407-08-05 Demonstration Code ================
-- Create and populate Employees table
USE [MyDatabase];
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO

/* DROP Table [dbo].[Employees] */
CREATE TABLE [dbo].[Employees](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL,
[Salary] [int] NULL,
[ManagerID] [int] NULL
) ON [PRIMARY]
GO

INSERT INTO [dbo].[Employees] ([Name], [Salary], [ManagerID])
VALUES ('John Smith',10000,3);
INSERT INTO [dbo].[Employees] ([Name], [Salary], [ManagerID])
VALUES ('Jane Anderson',12000,3);
INSERT INTO [dbo].[Employees] ([Name], [Salary], [ManagerID])
VALUES ('Tom Lanon',15000,4);
INSERT INTO [dbo].[Employees] ([Name], [Salary])
VALUES ('Anne Connor',20000);
INSERT INTO [dbo].[Employees] ([Name], [Salary], [ManagerID])
VALUES ('Jeremy York',9000,1);

Each employee has his/her own Id, which is our “Column X.” For a given employee (i.e., row), the column ManagerId contains the Id of his or her manager; this is our “Column Y.” If we trace the employee-manager pairs in this table using these columns:

  • The manager of the employee John Smith is the employee with Id 3, i.e., Tom Lanon.
  • The manager of the employee Jane Anderson is the employee with Id 3, i.e., Tom Lanon.
  • The manager of the employee Tom Lanon is the employee with Id 4, i.e., Anne Connor.
  • The employee Anne Connor does not have a manager; her ManagerId is null.
  • The manager of the employee Jeremy York is the employee with Id 1, i.e., John Smith.

This type of table structure is very common in hierarchies. Now, to show the name of the manager for each employee in the same row, we can run the following query:

-- 0407-08-10 Demonstration Code ================
-- Show the name of the manager for each employee
SELECT
employee.[Id],
employee.[Name],
employee.[ManagerId],
manager.[Name] as ManagerName
FROM Employees employee
JOIN Employees manager
ON employee.[ManagerId] = manager.[Id]

SelfJoin01

The query selects the columns Id, FullName, and ManagerId from the table aliased employee. It also selects the FullName column of the table aliased manager and designates this column as ManagerName. As a result, every employee who has a manager is output along with his/her manager’s ID and name.

In this query, the Employees table is joined with itself and has two different roles:

  • Role 1: It stores the employee data (alias employee).
  • Role 2: It stores the manager data (alias manager).


By doing so, we are essentially considering the two copies of the Employees table as if they are two distinct tables, one for the employees and another for the managers.

Table Aliases in Self Join

When referring to the same table more than once in an SQL query, we need a way to distinguish each reference from the others. For this reason, it is important to use aliases to uniquely identify each reference of the same table in an SQL query. As a good practice, the aliases should indicate the role of the table for each specific reference in a query.

If we want to list all the employees whether or not they have managers, we can use a LEFT OUTER JOIN instead. The query below does this:

-- 0407-08-15 Demonstration Code ================
-- Show all the employees whether or not they have managers
SELECT
e.[Id],
e.[Name],
e.[ManagerId],
m.[Name] as ManagerName
FROM Employees e
LEFT OUTER JOIN Employees m
ON e.[ManagerId] = m.[Id]

SelfJoin02

People Example

Create the table and data for the demonstration
-- 0407-08-20 Demonstration Code ================
-- Create and populate People table
USE [MyDatabase];
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO

/* DROP Table [dbo].[People] */
CREATE TABLE [dbo].[People](
[ID] [int] IDENTITY(1,1) NOT NULL,
[GivenName] [varchar](50) NULL,
[Age] [int] NULL,
[ParentID] [int] NULL
) ON [PRIMARY]
GO

SET IDENTITY_INSERT [dbo].[People] ON;
INSERT INTO [dbo].[People] ([ID], [GivenName], [Age], [ParentID])
VALUES('1', 'Jonathan', '5', '3')
INSERT INTO [dbo].[People] ([ID], [GivenName], [Age], [ParentID])
VALUES('2', 'Alexandra', '7', '3')
INSERT INTO [dbo].[People] ([ID], [GivenName], [Age], [ParentID])
VALUES('3', 'Barbara', '30', '')
SET IDENTITY_INSERT [dbo].[People] OFF;

In the query below, the children are assigned to their respective parents by joining the People table to itself:

-- 0407-08-25 Demonstration Code ================
-- Children are assigned to their respective parents by joining the People table to itself
SELECT
child.Id as ChildId,
child.GivenName as ChildFirstName,
child.Age as ChildAge,
child.ParentId,
parent.GivenName as ParentFirstName,
parent.age as ParentAge
FROM People child
INNER JOIN People parent
ON child.ParentId = parent.Id

SelfJoin03

Reference:
https://learnsql.com/blog/what-is-self-join-sql/?gad_source=1&gclid=Cj0KCQjwsaqzBhDdARIsAK2gqnck1rzY_7poLfkxZvqQWTWV3m4YmlSkiE9en3R6h3jhTfkLVC48U6UaAmdnEALw_wcB

 

Advanced Joins

Relational data is data that has a relationship with other data. In most cases the data is contained in separate tables.

Common relationships are one-to-one, one-to-many and many-to many.

one-to-one -- This is data that a single record in a table is related to a single record in another table.
one-to-many -- This is data that a single record in a table is related to one or more records in another table. The reverse relationship of many-to-one automatically exists.
many-to-many -- This is data that one or more records in a table is related to one or more records in another table.

The previous join examples focused on one-to-many relationships. By combining joins, more complex relationships can be represented. many-to-many relationships require 2 or more joins.

School Data

Students attending a school may study multiple subjects, each subject may have multiple students attending. In order to efficiently store the related data we need a table to store the student information and a table to store the subject information. In order to have a many-to-many relationship a third table is needed. This may be referred to as a link table. This table will contain information that links.loins the students and subjects.

These tables will be used to illustrate a many-to-many join. Execute this script to create the tables and data.

-- 0408-01-05 Demonstration Code ================
--Create and populate Students table
IF OBJECT_ID('Students') IS NULL
BEGIN
CREATE TABLE [dbo].[Students](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[Description] [nvarchar](50) NOT NULL
) ON [PRIMARY]
INSERT INTO [dbo].[Students] ([Name], [Description]) SELECT 'Allen','Senior'
INSERT INTO [dbo].[Students] ([Name], [Description]) SELECT 'Barry','Senior'
INSERT INTO [dbo].[Students] ([Name], [Description]) SELECT 'Carl','Junior'
END
GO

--Create and populate Subjects table
IF OBJECT_ID('Subjects') IS NULL
BEGIN
CREATE TABLE [dbo].[Subjects](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Subject] [nvarchar](50) NOT NULL,
[Description] [nvarchar](50) NOT NULL
) ON [PRIMARY]
INSERT INTO [dbo].[Subjects] ([Subject], [Description]) SELECT 'History', 'Germany'
INSERT INTO [dbo].[Subjects] ([Subject], [Description]) SELECT 'Math', ' Algebra'
INSERT INTO [dbo].[Subjects] ([Subject], [Description]) SELECT 'Science','Physics'
END

--Create and populate Stu2Sub link table
IF OBJECT_ID('Stu2Sub') IS NULL
BEGIN
CREATE TABLE [dbo].[Stu2Sub](
[ID] [int] IDENTITY(1,1) NOT NULL,
[StudentID] [int] NOT NULL,
[SubjectID] [int] NOT NULL
) ON [PRIMARY]
INSERT INTO [dbo].[Stu2Sub] ([StudentID], [SubjectID]) SELECT '1', '1' -- Allen, History
INSERT INTO [dbo].[Stu2Sub] ([StudentID], [SubjectID]) SELECT '1', '3' -- Allen, Physics
INSERT INTO [dbo].[Stu2Sub] ([StudentID], [SubjectID]) SELECT '2', '1' -- Barry, History
INSERT INTO [dbo].[Stu2Sub] ([StudentID], [SubjectID]) SELECT '2', '2' -- Barry, Math
INSERT INTO [dbo].[Stu2Sub] ([StudentID], [SubjectID]) SELECT '3', '2' -- Carl, Math
END

Querying the School Data

-- 0408-02-05 Demonstration Code ================
-- Subjects by Student
SELECT stu.[Name], [Subject]
FROM [Subjects] sub
JOIN [Stu2Sub] s2s
ON s2s.[SubjectID] = sub.ID
JOIN [Students] stu
ON stu.ID = s2s.[StudentID]
--WHERE stu.[Name] = 'Allen'
WHERE stu.[Name] = 'Barry'

SchoolData01

-- 0408-02-10 Demonstration Code ================
-- Students by Subject
SELECT sub.[Subject], [Name]
FROM [Students] stu
JOIN [Stu2Sub] s2s
ON s2s.[StudentID] = stu.ID
JOIN [Subjects] sub
ON sub.ID = s2s.[SubjectID]
WHERE sub.[Subject] = 'History'

SchoolData02