ako_funguju_joiny.sql

USE TestDB

-- SQL Join visualization
-- http://www.codeproject.com/KB/database/Visual_SQL_Joins/Visual_SQL_JOINS_orig.jpg

DROP TABLE [dbo].Authors
DROP TABLE [dbo].Books

CREATE TABLE [dbo].[Authors](
  [id] [int] IDENTITY(1,1) NOT NULL,
	[name] [varchar](max) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE TABLE [dbo].[Books](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[author_id] [int] NULL,
	[name] [varchar](max) NOT NULL,
	[published_at] [datetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


SET IDENTITY_INSERT [dbo].[Authors] ON 
INSERT [dbo].[Authors] ([id], [name]) VALUES (1, N'Radovan Ulej')
INSERT [dbo].[Authors] ([id], [name]) VALUES (2, N'Ahmed Al Hafoudh')
INSERT [dbo].[Authors] ([id], [name]) VALUES (3, N'Mikulas Magdolinic')
INSERT [dbo].[Authors] ([id], [name]) VALUES (4, N'Marek Vrabel')
SET IDENTITY_INSERT [dbo].[Authors] OFF


SET IDENTITY_INSERT [dbo].[Books] ON 
INSERT [dbo].[Books] ([id], [author_id], [name], [published_at]) VALUES (2, 1, N'Radova kniha o Avayai', CAST(0x0000A13D00000000 AS DateTime))
INSERT [dbo].[Books] ([id], [author_id], [name], [published_at]) VALUES (4, 1, N'Radova kniha o IPOffice', CAST(0x0000A04900000000 AS DateTime))
INSERT [dbo].[Books] ([id], [author_id], [name], [published_at]) VALUES (5, 1, N'Radova kniha o harmonikach', CAST(0x00009D4D00000000 AS DateTime))
INSERT [dbo].[Books] ([id], [author_id], [name], [published_at]) VALUES (6, 2, N'Ahmedova kniha o Ruby', CAST(0x00009C2500000000 AS DateTime))
INSERT [dbo].[Books] ([id], [author_id], [name], [published_at]) VALUES (7, 3, N'Mikiho kniha o stanze', CAST(0x000095D800000000 AS DateTime))
INSERT [dbo].[Books] ([id], [author_id], [name], [published_at]) VALUES (8, NULL, N'Niekoho kniha o nevieme com', CAST(0x0000918600000000 AS DateTime))
SET IDENTITY_INSERT [dbo].[Books] OFF

SELECT * FROM Authors a LEFT JOIN Books b ON a.id = b.author_id
SELECT * FROM Authors a LEFT JOIN Books b ON a.id = b.author_id WHERE b.author_id IS NULL
SELECT * FROM Authors a INNER JOIN Books b ON a.id = b.author_id
SELECT * FROM Authors a FULL OUTER JOIN Books b ON a.id = b.author_id
SELECT * FROM Authors a RIGHT JOIN Books b ON a.id = b.author_id
SELECT * FROM Authors a RIGHT JOIN Books b ON a.id = b.author_id WHERE a.id IS NULL
SELECT * FROM Authors a FULL OUTER JOIN Books b ON a.id = b.author_id WHERE a.id IS NULL OR b.author_id IS NULL