This post has already been read 830 times!

SUMMARY : In this mini-tutorial, you will learn how to query data from multiple tables using T-SQL JOINS statement.

  • A SQL JOIN combines records from two tables.
  • A JOIN locates related column values in the two tables.
  • A query can contain zero, one, or multiple JOIN operations.
  • INNER JOIN is the same as JOIN; the keyword INNER is optional.
Create the Database Model

IF OBJECT_ID(N'[joins].[Persons]', N'S') IS NOT NULL 
	DROP SCHEMA [joins]
GO

-- Create table Persons
IF OBJECT_ID(N'[joins].[Persons]', N'U') IS NOT NULL   
	DROP TABLE [joins].[Persons]
CREATE TABLE [joins].[Persons]
(
	Id INT NOT NULL PRIMARY KEY IDENTITY(1,1),
	Username nvarchar(20) NULL,
	Color_Id int NULL,
	Food_Id int NULL
)

-- Create table Colors
IF OBJECT_ID(N'[joins].[Colors]', N'U') IS NOT NULL   
	DROP TABLE [joins].[Colors]
CREATE TABLE [joins].[Colors]
(
	Id INT NOT NULL PRIMARY KEY,	
	Color nvarchar(20) NULL
)

-- Create table Foods
IF OBJECT_ID(N'[joins].[Colors]', N'U') IS NOT NULL   
	DROP TABLE [joins].[Foods]
CREATE TABLE [joins].[Foods]
(
	Id INT NOT NULL PRIMARY KEY,	
	Food nvarchar(20) NULL
)
GO

-- TRUNCATE TABLES 
TRUNCATE TABLE [joins].[Persons]
TRUNCATE TABLE [joins].[Colors]
TRUNCATE TABLE [joins].[Foods]
GO

-- INSERT VALUES
INSERT INTO [joins].[Persons] (Username, Color_Id, Food_Id) VALUES ('Steeve',1,NULL), ('Aaron',3,NULL), ('Mary',2,NULL)
																   , ('Fred',1,NULL), ('Anne',5,NULL), ('Beth',8,1), ('Johnny', NULL, 1), ('Karen',NULL,2)
INSERT INTO [joins].[Colors]  (Id, Color) VALUES (1,'red'),(2,'green'),(3,'blue'),(4,'pink'),(5,'purple'), (6,'mauve'), (7,'orange'), (8,'yellow'), (9,'indigo')
INSERT INTO [joins].[Foods]   (Id, Food) VALUES (1,'pizza'),(2,'burger'),(3,'sushi')
GO

Leave a Reply

Post Navigation