Overview

Introduction

The Using Joins module provides you with the instructions and devices to develop your hands-on skills in the following topics.

  • Using INNER JOIN
  • Working with LEFT OUTER JOIN
  • Using RIGHT OUTER JOIN
  • Working with FULL OUTER JOIN
  • Using SELF JOIN
  • Working with CROSS JOIN
  • Demonstrating the method to use more than one JOIN with AND operator
  • Demonstrating the method to compare fields to NULL values using JOIN

Lab time: It will take approximately 60 minutes to complete this lab.

Exam Objectives

The following exam objectives are covered in this lab:

  • Write and execute a Transact-SQL query with INNER JOIN
  • Write and execute a Transact-SQL query with LEFT OUTER JOIN
  • Write and execute a Transact-SQL query RIGHT OUTER JOIN
  • Write and execute a Transact-SQL query with FULL OUTER JOIN
  • Write and execute a Transact-SQL query with SELF JOIN
  • Write and execute a Transact-SQL query with CROSS JOIN
  • Write and execute a query with more than one JOIN operator to join three tables using AND - operator
  • Write and execute a query with JOIN operator to fetch rows by comparing fields with NULL values

Exercise 1 - Using INNER JOIN

An INNER JOIN retrieves rows from two tables with matching columns or fields. For example, if Table A and Table B have a common field EmployeeName, INNER JOIN r will combine and retrieve the rows where EmployeeName of Table A matches the Employee_Name of Table B. The syntax for using INNER JOIN is as follows:

Select columnname From table A JOIN table B ON table A.columnname = table B.column_name

In this exercise, you will learn to use INNER JOIN.

Exercise 2 - Working with LEFT OUTER JOIN

The LEFT OUTER JOIN or LEFT JOIN retrieves all the records from the left table or the first table with the matching records from the right table or the second table. It retrieves only those records where the value of a field from one table is equal to the value of the same field in the second table. If there are no matching records, the result will be NULL.

The syntax to use LEFT OUTER JOIN is as follows:

SELECT columnname FROM table A LEFT OUTER JOIN table B ON table A.columnname = table B.columnname

In this exercise, you will learn to use LEFT OUTER JOIN or LEFT JOIN.

Exercise 3 - Using RIGHT OUTER JOIN

RIGHT OUTER JOIN or RIGHT JOIN displays all the records in the second table or right table with records of the first table or left table where the common field values in both the tables are equal. The unmatched records are displayed as NULL.

In this exercise, you will learn to use RIGHT OUTER JOIN.

Exercise 4 - Working with FULL OUTER JOIN

FULL OUTER JOIN combines and retrieves all the records of the left table or first table and right table or second table.

The syntax for FULL OUTER JOIN is as follows:

SELECT columnname FROM table A FULL OUTER JOIN tableB ON table A.columnname=table B.column_name In this exercise, you will learn to use FULL OUTER JOIN.

Exercise 5 - Using SELF JOIN

SELF JOIN joins a table to itself. The result set displays two tables. SELF JOIN allows you to rename one of the tables temporarily in the SQL statement.

In this exercise, you will learn to use SELF JOIN.

Exercise 6 - Working with CROSS JOIN

CROSS JOIN performs a Cartesian product operation on the specified number of tables. Cartesian product refers to the product of a number of rows in the first table with the number of rows in the second table and so on.

In this exercise, you will learn to use CROSS JOIN.

Exercise 7 - Demonstrating the Method to Use More Than One JOIN with AND Operator

In this exercise, you will learn to use more than one JOIN along with AND operator to join a multiple number of tables.

Exercise 8 - Demonstrating the Method to Compare Fields to NULL Values Using JOIN

In this exercise, you will learn to use JOIN to fetch records from multiple tables by comparing fields to NULL values.

Comprehensive Learning

See the full benefits of our immersive learning experience with interactive courses and guided career paths.