Stephen's Wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== SQL Cheat Sheet ======

For Postgres SQL, see [[postgres_sql_cheat_sheet|Postgres SQL Cheat Sheet]]

===== CREATE =====

<code sql>
CREATE TABLE
	Table1 (
		  Id       int      NOT NULL
		, SomeDate datetime NULL
		CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED ( Id ASC )
	)
</code>

===== INSERT =====

<code sql>
-- Combining an INSERT with a SELECT.
INSERT
	Table1 (
		  Col1
		, Col2
		, Col3
	)
SELECT
	  Table2.Col1
	, Table2.Col2
	, Table2.Col3
FROM
	Table2

-- INSERTing multiple rows.
INSERT INTO Table1
	  (Col1,  Col2,  Col3)
VALUES
	  (Data1, Data2, Data3)
	, (Data1, Data2, Data3)
</code>

===== UPDATE =====

<code sql>
-- Combining an UPDATE with a SELECT.
UPDATE
	Table1
SET
	  Table1.Col1 = Table2.Col1
	, Table1.Col2 = Table2.Col2
FROM
	Table1
INNER JOIN
	Table2
ON
	Table2.Id = Table1.Id
</code>

===== DELETE =====

<code sql>
-- Combining an UPDATE with a JOIN.
DELETE
	Table1
FROM
	Table1
INNER JOIN
	Table2
ON
	Table2.Id = Table1.Id
</code>

===== Nested SELECT Statement =====

<code sql>
SELECT
	BestEmployee.Name
FROM (
	SELECT TOP 100
		*
	FROM
		Employee
	ORDER BY
		Employee.Sales DESC
) AS BestEmployee
ORDER BY
	BestEmployee.Name
</code>

===== Common Table Expression (CTE) =====

<code sql>
-- Untested code.
WITH BestEmployee_CTE
AS
(
	SELECT TOP 100
		*
	FROM
		Employee
	ORDER BY
		Employee.Sales DESC
)
SELECT
	BestEmployee.Name
FROM
	BestEmployee_CTE
</code>

===== Table Variable =====

<code sql>
-- Use *only* if a nested SELECT or CTE won't do the job.
DECLARE @SpecialEmployees TABLE
(
	EmployeeID INT
)

INSERT
	@SpecialEmployees (EmployeeID)
SELECT
	Employee.EmployeeID
FROM
	Employee
</code>

===== Cascading Delete (work in progress) =====

<code sql>
DECLARE @TargetTable VARCHAR(MAX) = 'Sale'
DECLARE @maxExpansions INT = 600

DECLARE @FamilyTree TABLE (
	  ObjectId   INT          NOT NULL
	, TableName  VARCHAR(max) NOT NULL
	, IsExpanded BIT          NOT NULL
	, [Path]     VARCHAR(max) NOT NULL
)

INSERT 
	@FamilyTree (ObjectId, TableName, IsExpanded, [Path])
SELECT
	tables.object_id
	, tables.[name]
	, 0
	, tables.[name]
FROM
	sys.tables
WHERE
	sys.tables.[name] = @TargetTable

DECLARE @currentObjectId INT
DECLARE @currentExpansion INT = 0

SELECT TOP 1
	@currentObjectId = [@FamilyTree].ObjectId
FROM
	@FamilyTree
WHERE
	[@FamilyTree].IsExpanded = 0

WHILE @currentObjectId IS NOT NULL AND @currentExpansion < @maxExpansions
BEGIN
	INSERT
		@FamilyTree (ObjectId, TableName, IsExpanded, [Path])
	SELECT DISTINCT
		  ChildTable.object_id
		, ChildTable.[name]
		, 0
		, CONCAT ([@FamilyTree].[Path], ' > ', ChildTable.[name])
	FROM
		@FamilyTree
	JOIN
		sys.foreign_keys ForeignKey
		ON ForeignKey.parent_object_id = [@FamilyTree].ObjectId
	JOIN
		sys.tables ChildTable
		ON ChildTable.object_id = ForeignKey.referenced_object_id
	WHERE
		[@FamilyTree].ObjectId = @currentObjectId

	UPDATE
		@FamilyTree
	SET
		[@FamilyTree].IsExpanded = 1
	WHERE
		[@FamilyTree].ObjectId = @currentObjectId

	SELECT TOP 1
		@currentObjectId = [@FamilyTree].ObjectId
	FROM
		@FamilyTree
	WHERE
		[@FamilyTree].IsExpanded = 0

	SELECT @currentExpansion = @currentExpansion + 1
END

SELECT DISTINCT
	*
	, CONCAT(
		'DELETE ['
		, [@FamilyTree].TableName
		, '] -- '
		, [@FamilyTree].[Path]
	)
FROM
	@FamilyTree
ORDER BY
	[@FamilyTree].[Path] DESC
</code>

===== Copy a Table =====

<code sql>
-- Doesn't copy keys or constraints.
CREATE TABLE 
	NewTable
AS
SELECT
	*
FROM
	SourceTable
-- Omit, if data is also to be copied.
WHERE
	1=2;
</code>

===== Alter a Table =====

==== Add a column ====

<code sql>
ALTER TABLE Table_Name
	ADD Column_Name INT NULL;
</code>

==== Add a column if it doesn't exist ====

<code sql>
IF COL_LENGTH('Table_Name','Column_Name') IS NULL
BEGIN
	ALTER TABLE Table_Name
		ADD Column_Name INT NULL;
END
</code>

==== Rename a column ====

<code sql>
EXEC sp_rename 'Categories.Active', 'Active_Old', 'COLUMN'
</code>

===== Create or alter a stored procedure =====

If you want to preserve permissions:

<code sql>
IF OBJECT_ID('StoredProcedure_Name') IS NULL
	EXEC ('CREATE PROCEDURE StoredProcedure_Name AS SELECT 1')
GO
ALTER PROCEDURE StoredProcedure_Name
(
...
</code>

If you are happy to blow away permissions:

<code sql>
IF OBJECT_ID('StoredProcedure_Name', 'P') IS NOT NULL
	DROP PROC StoredProcedure_Name

GO
CREATE PROCEDURE StoredProcedure_Name
(
...
</code>

===== Determine the Data Types in Results of a Query =====

<code sql>
SELECT * FROM sys.dm_exec_describe_first_result_set(
   N'EXEC SomeStoredProcedure 1234', NULL, 0);
</code>

===== Sort out Collation =====

<code sql>
-- Determine the default collation for the server.
SELECT serverproperty('collation')

-- Convert a varchar to a specific collation.
SELECT CAST('abc' AS VARCHAR(5)) COLLATE Latin1_General_CI_AS
</code>

===== Duplicate (copy) a Database =====

<code sql>
BACKUP DATABASE SourceDatabase
TO DISK = 'E:\Backups\20150609 SourceDatabase.bak'
WITH COPY_ONLY, INIT

CREATE DATABASE NewDatabase

-- Use the LogicalName column to populate the MOVE clause of the RESTORE DATABASE command.
RESTORE FILELISTONLY FROM DISK = 'E:\Backups\20150609 SourceDatabase.bak' WITH FILE = 1

-- Use the physical_name column to populate the TO clause of the RESTORE DATABASE command.
SELECT
	master_files.physical_name
FROM
	master.sys.databases
JOIN
	master.sys.master_files
ON
	master_files.database_id = databases.database_id
WHERE
	databases.name = 'NewDatabase'

RESTORE DATABASE NewDatabase
FROM DISK = 'E:\Backups\20150609 SourceDatabase.bak'
WITH REPLACE
	, MOVE 'SourceDatabase' TO N'E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\NewDatabase.mdf'
	, MOVE 'SourceDatabase_log' TO N'E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\NewDatabase_log.ldf'

-- Compare the databases. No results = no differences.
SELECT
    *
FROM
    SourceDatabase.sys.objects SourceObjects
FULL JOIN
    NewDatabase.sys.objects NewObjects
    ON NewObjects.object_id = SourceObjects.object_id
WHERE
    SourceObjects.object_id IS NULL OR NewObjects.object_id IS NULL
</code>

===== Export a .bacpac By Command Line =====

Install SqlPackage:
<code>
dotnet tool install -g microsoft.sqlpackage
</code>

Export .backpac. Only add the ''Encrypt=False'' for local exports.
<code>
SqlPackage /Action:Export /TargetFile:"D:\Dev\BucketsOfFundsSQL\$((Get-Date).ToString("yyyyMMdd")) Buckets.bacpac" /SourceConnectionString:"Integrated Security=SSPI;Persist Security Info=False;Encrypt=False;Initial Catalog=Buckets;Data Source=."
</code>

===== Close all database connections =====

<code sql>
ALTER DATABASE DatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

-- Do this straight away, because it sucks if a database is left in single user mode.
ALTER DATABASE DatabaseName SET MULTI_USER 
GO
</code>

===== Find stuff in all Databases =====

<code sql>
-- Find a table in all databases.
sp_msforeachdb @command1='USE ?;SELECT DB_NAME() AS [Database], * FROM sys.Tables WHERE name LIKE ''TableName'''
</code>

<code sql>
-- Find a table column in all databases.
sp_msforeachdb @command1='USE ?;SELECT DB_NAME() AS [Database], t.name AS [Table], * FROM sys.Columns c JOIN sys.Tables t ON t.object_id = c.object_id WHERE c.name LIKE ''ColumnName'''
</code>

===== Compare two Databases =====

<code sql>
-- No results = no differences. This is only suitable for databases that have been duplicated *exactly*.
SELECT
    *
FROM
    SourceDatabase.sys.objects SourceObjects
FULL JOIN
    TargetDatabase.sys.objects TargetDatabase
    ON TargetDatabase.object_id = SourceObjects.object_id
WHERE
    SourceObjects.object_id IS NULL OR TargetDatabase.object_id IS NULL
</code>
===== Most Recent =====

<code sql>
SELECT *
FROM
	Foo
INNER JOIN
	History MostRecentHistory
	ON MostRecentHistory.FooId = Foo.Id
LEFT JOIN
	History AllHistory
	ON AllHistory.FooId = MostRecentHistory.FooId
	AND AllHistory.LastUpdateOn > MostRecentHistory.LastUpdateOn
WHERE
	AllHistory.Id IS NULL

-- If the Foo table may not have history and all results are still required:
SELECT *
FROM
	Foo
LEFT JOIN
	History MostRecentHistory
	ON MostRecentHistory.FooId = Foo.Id
LEFT JOIN
	History AllHistory
	ON AllHistory.FooId = MostRecentHistory.FooId
	AND AllHistory.LastUpdateOn > MostRecentHistory.LastUpdateOn
WHERE
	AllHistory.Id IS NULL
</code>

===== Table Variables =====

**Use a Nested SELECT Statement or a Common Table Expression instead!**

<code sql>
DECLARE @Results TABLE (
	  BananaId   INT
	, BananaName VARCHAR(MAX)
)
</code>

===== Stored Procedure into Table Variable =====

**Use a Nested SELECT Statement or a Common Table Expression instead!**

<code sql>
DECLARE @Results TABLE (
	  BananaId   int
	, BananaName varchar(max)
)

INSERT INTO
	@Results
EXEC
	GetRipeBananas
</code>

===== Foreign Keys =====

==== Display Foreign Key Relationships for a Table ====

Can do this, but it will only show which tables reference the specified table, not what tables the specified table references:

<code sql>
sp_fkeys TableName
</code>

Better to do this, which lists all foreign key relationships and lots of other information as well:

<code sql>
sp_help TableName
</code>

==== Display All Foreign Key Info ====

<code sql>
SELECT
	  ForeignKey.[name]    ForeignKey
	, PrimaryTable.[name]  PrimaryTable
	, PrimaryColumn.[name] PrimaryColumn
	, ForeignTable.[name]  ForeignTable
	, ForeignColumn.[name] ForeignColumn
FROM
	sys.foreign_key_columns ForeignKeyColumn
JOIN
	sys.foreign_keys ForeignKey
	ON ForeignKey.object_id = ForeignKeyColumn.constraint_object_id
JOIN
	sys.columns PrimaryColumn
	ON PrimaryColumn.object_id = ForeignKeyColumn.referenced_object_id
	AND PrimaryColumn.column_id = ForeignKeyColumn.referenced_column_id
JOIN
	sys.tables PrimaryTable
	ON PrimaryTable.object_id = ForeignKeyColumn.referenced_object_id
JOIN
	sys.columns ForeignColumn
	ON ForeignColumn.object_id = ForeignKeyColumn.parent_object_id
	AND ForeignColumn.column_id = ForeignKeyColumn.parent_column_id
JOIN
	sys.tables ForeignTable
	ON ForeignTable.object_id = ForeignKeyColumn.parent_object_id
ORDER BY
	  PrimaryTable.[name]
	, PrimaryColumn.[name]
	, ForeignTable.[name]
	, ForeignColumn.[name]
</code>

===== Display All Table Column Info =====

<code sql>
SELECT
	  [Schema].[name]      SchemaName
	, [Table].[name]       TableName
	, [Column].[name]      ColumnName
	, [ColumnType].[name]  ColumnType
	, [Column].max_length  [MaxLength]
	, [Column].[precision] [Precision]
	, [Column].is_nullable Nullable
FROM
	sys.tables [Table]
JOIN
	sys.schemas [Schema]
	ON [Schema].schema_id = [Table].schema_id
JOIN
	sys.columns [Column]
	ON [Column].object_id = [Table].object_id
JOIN
	sys.types [ColumnType]
	ON [ColumnType].system_type_id = [Column].system_type_id
ORDER BY
	  [Schema].[name]
	, [Table].[name]
	, [Column].[name]
</code>

===== Search All Text in a Database =====

<code sql>
DECLARE @searchText NVARCHAR(20) = N'Foo';
DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql = @sql +
	CONCAT(
		'SELECT '''
		, [Table].[name]
		, ''' AS [Table], '''
		, [Column].[name]
		, ''' AS [Column], ['
		, [Table].[name]
		, '].['
		, [Column].[name]
		, '] AS [Data] FROM '
		, [Table].[name]
		, ' WHERE ['
		, [Table].[name]
		, '].['
		, [Column].[name]
		, '] LIKE ''%'
		, @searchText
		, '%'''
		, CHAR(13) + CHAR(10)
		, 'UNION ALL'
		, CHAR(13) + CHAR(10)
	)
FROM
	sys.columns AS [Column]
JOIN
	sys.tables AS [Table]
	ON [Table].object_id = [Column].object_id
JOIN
	sys.systypes AS ColumnType
	ON ColumnType.xtype = [Column].system_type_id
WHERE
	[ColumnType].[name] IN ('nchar', 'ntext', 'nvarchar', 'text', 'varchar')
ORDER BY
	  [Table].[name]
	, [Column].[name]

SELECT @sql = SUBSTRING(@sql,1, LEN(@sql) - 11);
EXEC (@sql)</code>

==== Alternative ====

  - Run the SQL below.
  - Copy the results into the editor.
  - Remove the last ''UNION ALL''.
  - Append the bit at the beginning.

<code sql>
-- DECLARE @searchText NVARCHAR(100) = N'%foo%';
SELECT
	CONCAT(
		  'SELECT '''
		, [TABLE].[name]
		, ''' AS [Table], '''
		, [COLUMN].[name]
		, ''' AS [Column], ['
		, [TABLE].[name]
		, '].['
		, [COLUMN].[name]
		, '] COLLATE '
		, convert(NVARCHAR, serverproperty('collation'))
		, ' AS [Data] FROM ['
		, [TABLE].[name]
		, '] WHERE ['
		, [TABLE].[name]
		, '].['
		, [COLUMN].[name]
		, '] LIKE @searchText UNION ALL'
	)
FROM
	sys.columns AS [COLUMN]
JOIN
	sys.tables AS [TABLE]
	ON [TABLE].object_id = [COLUMN].object_id
JOIN
	sys.systypes AS ColumnType
	ON ColumnType.xtype = [COLUMN].system_type_id
JOIN
	sys.schemas AS [Schema]
	ON [Schema].schema_id = [TABLE].schema_id
WHERE
	[ColumnType].[name] IN ('nchar', 'ntext', 'nvarchar', 'text', 'varchar')
	AND [TABLE].is_ms_shipped = 0
	AND [Schema].[name] = 'dbo'
ORDER BY
	  [TABLE].[name]
	, [COLUMN].[name]
</code>
===== Randomness =====

<code sql>
SELECT ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) AS RandomNumber

SELECT LEFT(CAST(NEWID() AS VARCHAR(100)), 6) AS RandomString

-- Semi-random, good for randomish sorting, returns varbinary(8000)
SELECT HashBytes('MD5', CONCAT('Seed', Table.TableId))

-- Same thing, but returns varchar(32)
SELECT CONVERT(VARCHAR(32), HashBytes('MD5', CONCAT('Seed', Table.TableId)), 2)
</code>

===== Determine Free Disk Space on SQL Server =====

<code sql>
EXEC sys.xp_fixeddrives
</code>

===== Does file exist on SQL Server =====

<code sql>
-- Just for general info
EXEC master.dbo.xp_fileexist N'\\PVW-ENT-SQL32\Backups\SMS.bak'

-- Part of a script that needs to make a decision.
DECLARE @result INT
EXEC master.dbo.xp_fileexist N'\\PVW-ENT-SQL32\Backups\SMS.bak', @result OUTPUT
IF @result = 1
	PRINT 'File exists.'
ELSE
	PRINT 'File does not exist.'
</code>

===== Determine Contents of a Directory on SQL Server =====

<code sql>
EXEC Master.dbo.xp_DirTree 'C:',1,1
</code>

===== Fix Logins After DB Restore =====

<code sql>
-- Replace username with the actual username.
USE DATABASE
EXEC sp_change_users_login 'report'
EXEC sp_change_users_login 'auto_fix', 'CIPLogin' -- This definitely works.
-- Or try:
EXEC sp_change_users_login @Action = 'auto_fix', @UserNamePattern = 'User', @Password = 'Password'
EXEC sp_change_users_login 'update_one', username, username
-- Quantate
USE QuantateAdministration
EXEC sp_change_users_login 'update_one', 'QuantateSysAdmin', 'Quantate_Login'
-- CIP
USE CIP
EXEC sp_change_users_login 'update_one', 'CIPUser', 'CIPLogin'
-- NGA Provisioning
USE NextGenProvDB
EXEC sp_change_users_login 'update_one', 'NextGenUser', 'NextGenLogin'
-- General. 
EXEC sp_change_users_login 'update_one', 'DBUser', 'ServerUser'
-- Server -> Security -> Logins -> *ServerUser* -> Right click -> Properties
-- User Mapping -> Find correct database, value in User col = *DBUser*
</code>

===== Accessing A Linked Server =====

<code sql>
SELECT TOP 100
	*
FROM
	OpenQuery (LinkedServerName, 'SELECT * FROM Users')
</code>


===== Creating a Login or User =====

This is particularly useful when the database is on Azure.

<code sql>
-- Create the Login.
-- The CHECK_EXPIRATION and CHECK_POLICY options relate to the password. Switch them off for a service or application type login.
CREATE LOGIN [OrchardUser] WITH PASSWORD=N'XXXXXXX', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

-- Create the User.
CREATE USER [OrchardUser] FOR LOGIN [OrchardUser] WITH DEFAULT_SCHEMA=[dbo]
GO

-- Grant a role.
EXEC sp_addrolemember N'db_owner', N'OrchardUser'
</code>

===== Nuke the Database =====

Generates SQL commands to drop everything in the database. Be careful you nuke the correct database...

<code sql>
USE [Database]

SELECT 'DROP PROCEDURE ' + [Schema].name + '.' + StoredProcedure.name AS SqlCommand
       , 10                                                           AS SortOrder
FROM   sys.objects StoredProcedure
JOIN   sys.schemas [Schema]
ON     [Schema].schema_id = StoredProcedure.schema_id
WHERE  StoredProcedure.TYPE = 'P'
UNION
SELECT 'DROP VIEW ' + sys.objects.name
       , 20
FROM   sys.objects
WHERE  sys.objects.TYPE = 'V'
UNION
SELECT 'ALTER TABLE ' + [ParentTable].Name + ' DROP CONSTRAINT [' + [ForeignKey].name + ']'
       , 30
FROM   sys.objects [ForeignKey]
JOIN   sys.objects [ParentTable]
ON     [ParentTable].object_id = [ForeignKey].parent_object_id
WHERE  [ForeignKey].TYPE = 'F'
UNION
SELECT 'DROP TABLE ' + sys.objects.name
       , 40
FROM   sys.objects
WHERE  sys.objects.TYPE = 'U'
ORDER BY SortOrder
</code>

===== Get a database out of Single-User Mode =====

<code sql>
EXEC sp_who

-- Find the spid of the connect that is connected to the database.
KILL 56

ALTER DATABASE [DatebaseName] SET MULTI_USER
</code>

===== Calling a Stored Procedure with ADO.NET =====

If only one result set is returned:

<code c#>
using (SqlConnection connection = new SqlConnection("Data Source=...")) {
	connection.Open();

	using (SqlCommand command = new SqlCommand("StoredProcedureName", connection)) {
		command.CommandType = CommandType.StoredProcedure;
		command.CommandTimeout = 300;

		using (SqlDataReader reader = command.ExecuteReader()) {
			while (reader.Read()) {
				for (int i = 0; i < reader.FieldCount; i++) {
					string fieldName  = reader.GetName(i);
					// Note that values must be accessed by the column index and can not be accessed directly by field name.
					string fieldValue = reader.GetValue(i);
					Console.WriteLine("{0}: {1}", fieldName, fieldValue);
				}
			}

			reader.Close();
		}
	}
}
</code>

If multiple result sets are returned:

<code c#>
using (SqlConnection connection = new SqlConnection("Data Source=...")) {
	connection.Open();

	using (SqlCommand command = new SqlCommand("StoredProcedureName", connection)) {
		command.CommandType = CommandType.StoredProcedure;
		command.CommandTimeout = 300;

		using (SqlDataReader reader = command.ExecuteReader()) {
			do {
				while (reader.Read()) {
					for (int i = 0; i < reader.FieldCount; i++) {
						string fieldName  = reader.GetName(i);
						// Note that values must be accessed by the column index and can not be accessed directly by field name.
						string fieldValue = reader.GetValue(i);
						Console.WriteLine("{0}: {1}", fieldName, fieldValue);
					}
				}

			// Move to the next set of results.
			} while (reader.NextResult());

			reader.Close();
		}
	}
}
</code>

===== Joins =====

^ Type ^ Diagram ^ Code Sample ^ Notes ^
| Inner Join | {{ :innerjoin.png |Inner Join}} | <code sql>SELECT
    *
FROM
    Foo
INNER JOIN
    Bar
    ON Bar.Id = Foo.BarId</code> | The most common type of join. |
| Left Join | {{ :leftjoin.png |Left Join}} | <code sql>SELECT
    *
FROM
    Foo
LEFT JOIN
    Bar
    ON Bar.Id = Foo.BarId</code> | The second most common type of join. |
| Right Join | {{ :rightjoin.png |Right Join}} | <code sql>SELECT
    *
FROM
    Foo
RIGHT JOIN
    Bar
    ON Bar.Id = Foo.BarId</code> | Rare type of join. |
| Full Join (or Outer Join) | {{ :fulljoin.png |Full Join}} | <code sql>SELECT
    *
FROM
    Foo
FULL JOIN
    Bar
    ON Bar.Id = Foo.BarId</code> <code sql>FULL OUTER JOIN</code> | Rare type of join. |
| Left Excluding Join | {{ :leftexcludingjoin.png |Left Excluding Join}} | <code sql>SELECT
    *
FROM
    Foo
LEFT JOIN
    Bar
    ON Bar.Id = Foo.BarId
WHERE
    Bar.Id IS NULL</code> | Uncommon type of join, but useful to avoid this: <code sql>SELECT
    *
FROM
    Foo
WHERE
    Foo.Id NOT IN (
        SELECT
            Bar.FooId
        FROM
            Bar
     )</code> which is slow. |
| Right Excluding Join | {{ :rightexcludingjoin.png |Right Excluding Join}} | <code sql>SELECT
    *
FROM
    Foo
RIGHT JOIN
    Bar
    ON Bar.Id = Foo.BarId
WHERE
    Foo.Id IS NULL</code> | Rare type of join. |
| Full Excluding Join | {{ :fullexcludingjoin.png |Full Excluding Join}} | <code sql>SELECT
    *
FROM
    Foo
FULL JOIN
    Bar
    ON Bar.Id = Foo.BarId
WHERE
    Foo.Id IS NULL OR Bar.Id IS NULL</code> | No known practical application at all. |