Skip to content Skip to main navigation Skip to footer

SQL Cross Joins and Natural Joins

Objectives

This article covers the following objectives:

  • Construct and execute a natural join using ANSI-99 SQL join syntax
  • Create a cross join using ANSI-99 SQL join syntax
  • Explain the importance of having a standard for SQL as defined by ANSI
  • Describe a business need for combining information from multiple data sources

Purpose

  • Up to now, your experience using SQL has been limited to querying and returning information from one database table at a time.
  • This would not be a problem if all data in the database were stored in only one table.
Obtaining Data from Multiple Tables
Data modeling that separating data
into individual tables

Some column names on the sample output have been abbreviated in order to fit on the page.

  • But you know from data modeling that separating data into individual tables and being able to associate the tables with one another is the heart of relational database design.
  • Fortunately, SQL provides join conditions that enable information to be queried from separate tables and combined in one report.

Join Commands

  • There are two sets of commands or syntax which can be used to make connections between tables in a database:
    • Oracle proprietary joins
    • ANSI/ISO SQL 99 compliant standard joins
  • In this course, you will learn to use both sets of join commands.
  • Oracle proprietary joins will be covered later in the course.

ANSI

  • ANSI stands for American National Standards Institute.
  • Founded in 1918, ANSI is a private, non-profit organization that administers and coordinates the U.S. voluntary standardization and conformity assessment system.
  • The Institute’s mission is to enhance both the global competitiveness of U.S. business and the U.S. quality of life by promoting and facilitating voluntary consensus standards and conformity assessment systems, and safeguarding their integrity.

SQL

  • Structured Query Language (SQL) is the information processing industry-standard language of relational database management systems (RDBMS).
  • The language was originally designed by IBM in the mid 1970’s, came into widespread use in the early 1980’s, and became an industry standard in 1986 when it was adopted by ANSI.
  • So far there have been three ANSI standardization of SQL, each one building on the previous one.
  • They are named after the year in which they were first proposed, and are widely known by their short names: ANSI-86, ANSI-92 and ANSI-99.

NATURAL JOIN

  • A SQL join clause combines fields from 2 (or more) tables in a relational database.
  • A natural join is based on all columns in two tables that have the same name and selects rows from the two tables that have equal values in all matched columns.
  • The employees table has a job_id column.
  • This is a reference to the column of the same name in the jobs table.
NATURAL JOIN
  • As shown in the sample code, when using a natural join, it is possible to join the tables without having to explicitly specify the columns in the corresponding table.
  • However, the names and data types of both columns must be the same.
SELECT first_name, last_name, job_id, job_title
FROM employees NATURAL JOIN jobs
WHERE department_id > 80;
  • This join will return columns from the employees table and their related job_title from the jobs table based on the common column job_id.

The WHERE clause was added to apply an additional restriction to one of the tables, to limit the rows of output.

SELECT first_name, last_name, job_id, job_title
FROM employees NATURAL JOIN jobs
WHERE department_id > 80;
NATURAL JOIN
  • Here is another example:
SELECT department_name, city
FROM departments NATURAL JOIN locations;
  • The departments and locations table both have a column, location_id, which is used to join the two tables.
  • Notice that the natural join column does not have to appear in the SELECT clause.
NATURAL JOIN

CROSS JOIN

  • The ANSI/ISO SQL: 1999 SQL CROSS JOIN joins each row in one table to every row in the other table.
  • The result set represents all possible row combinations from the two tables.
  • This could potentially be very large!
  • If you CROSS JOIN a table with 20 rows with a table with 100 rows, the query will return 2000 rows.

Cross Join Example

  • The employees table contains 20 rows and the departments table has 8 rows.
  • Performing a CROSS JOIN will return 160 rows.
SELECT last_name, department_name
FROM employees CROSS JOIN departments;
SQL Cross Joins and Natural Joins

The result set of a CROSS JOIN brings together data from two tables that are not logically related to each other.

CROSS JOINS are actually useful during testing to create large datasets to measure database performance.

Terminology

Key terms used in this article included:

  • Cross join
  • Natural join

Summary

In this article, you should have learned how to:

  • Construct and execute a natural join using ANSI-99 SQL join syntax
  • Create a cross join using ANSI-99 SQL join syntax
  • Explain the importance of having a standard for SQL as defined by ANSI
  • Describe a business need for combining information from multiple data sources

Was This Article Helpful?

0
Related Articles
0 Comments

There are no comments yet

Leave a comment

Your email address will not be published. Required fields are marked *