.NET/C#
  • ASP.NET Core Overview
  • ASP.NET Core API tutorial
    • How to create ASP.NET Core C# API tutorial
      • launchSettings
      • Install swagger
        • Swagger best practices
      • Run the app
      • Fix CORS problem
      • Add AutoMapper
      • Add JWT reference
      • Add .gitignore
      • Basic structure & EF Core
      • AddSingleton
    • Loading Related Entities
  • Unit test controller logic in ASP.NET Core
    • Unit testing controllers
  • .NET Q&A
    • dotnet try
    • LINQ
      • LINQ Query Syntax
      • Lambda Expression
      • Standard Query Operators
  • .NET C# Interview questions
    • C# - .NET interview questions and answers [Part 1]
    • C# - .NET interview questions and answers [Part 2]
    • C# Interview questions [Part 3] General questions
  • C#
    • Object-Oriented Programming (Principles)
      • N-tier architecture style
      • Command and Query Responsibility Segregation (CQRS) pattern
      • Project architecture
    • C# Advanced review
      • Implicit typing
      • Generics
      • Attributes
      • Reflection
      • Delegates
      • Anonymous Methods and Lambda Expressions
      • Events
      • Ref vs Out
      • Task
        • TaskFactory Class
  • MySQL
    • MySQL Lerning
      • SELECT
      • ORDER BY
      • WHERE
      • DISTINCT
      • IN
      • BETWEEN
      • Join
      • INNER JOIN
      • LEFT JOIN
      • RIGHT JOIN
      • GROUP BY
      • Subquery
      • UNION
    • Stored Procedures
      • CREATE PROCEDURE
  • Versioning API, MongoDB and ci-cd
    • Create a web API with ASP.NET Core and MongoDB
    • REST API versioning with ASP.NET Core
    • Design a CI/CD pipeline using Azure DevOps
      • Create a CI/CD pipeline for GitHub repo using Azure DevOps Starter
  • TFS & TFCV
    • What is Team Foundation Version Control
      • Develop and share your code in TFVC using Visual Studio
      • Suspend your work and manage your shelvesets
    • Newtonsoft Json.NET
      • Serializing and Deserializing JSON
  • MS-SQL
    • Quick tutorial
      • Add new column to a table
      • LEFT/RIGHT Reverse operator
      • Dates (Transact-SQL)
      • CAST and CONVERT (Transact-SQL)
      • Types of JOIN
      • Our first Left Outer Join
      • CROSS JOIN
Powered by GitBook
On this page
  • Some tips and tricks
  • Inner join
  • Complex JOINs

Was this helpful?

  1. MS-SQL
  2. Quick tutorial

Our first Left Outer Join

PreviousTypes of JOINNextCROSS JOIN

Last updated 4 years ago

Was this helpful?

SELECT * 
FROM 
	[Pets] LEFT JOIN [Owners]
ON 
	[Pets].OwnerID = [Owners].OwnerID
SELECT [Pets].[Name], [Owners].[Name]
FROM 
	[Pets] LEFT JOIN [Owners]
ON 
	[Pets].OwnerID = [Owners].OwnerID

Some tips and tricks

SELECT 
	A.[Name], B.[Name]
FROM 
	[Pets] AS A LEFT JOIN [Owners] AS B
ON 
	A.OwnerID = B.OwnerID
WHERE 
	LEFT(A.[Name], 1) = LEFT(B.[Name], 1)

Aliases for columns can be in two ways, let's check it out the first one:

-- ALIASES FOR COLUMNS
SELECT 
	A.[Name] AS PetName, B.[Name] AS OwnerName
FROM 
	[Pets] AS A LEFT JOIN [Owners] AS B
ON 
	A.OwnerID = B.OwnerID
WHERE 
	LEFT(A.[Name], 1) = LEFT(B.[Name], 1)

Or also you can write just as follows:

-- ALIASES FOR COLUMNS
-- No AS keyword for aliases
SELECT 
	A.[Name] PetName, B.[Name] OwnerName
FROM 
	[Pets] AS A LEFT JOIN [Owners] AS B
ON 
	A.OwnerID = B.OwnerID
WHERE 
	LEFT(A.[Name], 1) = LEFT(B.[Name], 1)
-- FULL CODE
-- ALIASES FOR TABLES
SELECT 
	A.[Name], B.[Name]
FROM 
	[Pets] AS A LEFT JOIN [Owners] AS B
ON 
	A.OwnerID = B.OwnerID
WHERE 
	LEFT(A.[Name], 1) = LEFT(B.[Name], 1)

-- ALIASES FOR COLUMNS
SELECT 
	A.[Name] AS PetName, B.[Name] AS OwnerName
FROM 
	[Pets] AS A LEFT JOIN [Owners] AS B
ON 
	A.OwnerID = B.OwnerID
WHERE 
	LEFT(A.[Name], 1) = LEFT(B.[Name], 1)

-- RIGHT JOINS
SELECT 
	A.[Name] AS PetName, B.[Name] AS OwnerName
FROM 
	[Owners] AS B RIGHT JOIN [Pets] AS A
ON 
	A.OwnerID = B.OwnerID

Inner join

-- INNER JOINS
SELECT * 
FROM [dbo].[Pets] AS A 
INNER JOIN [dbo].[ProceduresHistory] AS B
ON A.Petid = B.PetID

Complex JOINs

-- COMPLEX JOINS
SELECT * 
FROM
	[dbo].[Pets] AS A
INNER JOIN [dbo].[ProceduresHistory] AS B
ON 
	A.Petid = B.PetID
LEFT JOIN [dbo].[ProceduresDetails] AS C
ON
	B.ProcedureType = C.ProcedureType
AND
	B.ProcedureSubCode = C.ProcedureSubCode

Here we have combined 3 tables:

Pets, ProceduresHistory and ProceduresDetails