Computer Security, Programming, Technology, Tutorials, Web Security

Inner JOIN vs Cross JOIN In SQL Server

July 30, 2017

Introduction

The SQL language is difficult to learn for new coders. It’s unlike any other programming language. The most arduous part is comprehending what type of SQL statement to deploy to return the correct results. This article seeks to expound the difference between an INNER JOIN and a CROSS JOIN.

Using the correct JOIN statement in your SQL code is pivotal for definite results. While it may be painless to recognize a bad data set when solely a couple results are returned, it’s not as straightforward when you hold thousands of results. The syntax for INNER JOIN and CROSS JOIN are analogous. However, both statements generate fundamentally distinctive results. It’s valuable to grasp the differences to construct conclusive reports and user results from your data.

The INNER JOIN Statement

Let’s start with the INNER JOIN statement since it’s more easier to understand and the most prevalent. An INNER JOIN returns every row from joined tables where there is a counterpart.

Take the example of the customer and order. Every customer may have numerous orders, which labels the relationship a one-to-many. In a one-to-many relationship, every row is returned from the order table where there is a link to a customer record.

For instance, we will examine a query and the results.

Let’s write an INNER JOIN statement:

SELECT c.FirstName, c.LastName o.OrderId

FROM customer c

INNER JOIN order o on c.CustomerID = o.CustomerId

With the above INNER JOIN statement, we would perceive 10 orders that match a customer. We must remember that if a customer does not have an order, then we won’t perceive the customer in the result set. There has to be a counterpart in both tables for an INNER JOIN to return a record. You’ll employ INNER JOINs in many of your queries when you write SQL code for applications and report.

In the majority of cases, you’ll want to filter records utilizing the WHERE clause. We’ll add a WHERE clause to the statement and merely return records for a customer with an ID of 6. We are capable of perceiving all orders for this customer, which is merely 1 record according to the SQL output.

The CROSS JOIN Statement

A Cartesian Product

A CROSS JOIN statement is much more intricate, but once you grasp the result set, you’ll appreciate when you require one. A CROSS JOIN collects all records from one table and every record from a second table and awards you a Cartesian product. If you are not conversant with a Cartesian product, it refers to the multiplication of one set and a second set.

The first step is possessing a greater grasp of a Cartesian product utilizing our customer and order tables. You may compute the number of records in your result set by perceiving the number of records in every table. The results are consistently the number of records in the customer table multiplied by the number of records in the order table.

For example, if you retain 5 customers and 10 orders, the result set returned by the SQL Server includes 50 records. You should also remember that there is no rapport between the records like an INNER JOIN. Recall that an INNER JOIN links based on a primary-foreign vital connection. The Cartesian product links every record from one table with every record from the second indifferent of the relationship.

We’ll utilize a SQL command where the only alteration is the JOIN statement. Particularly, where the JOIN statement was previously INNER JOIN, and now it’s CROSS JOIN. As we’re not utilizing relationships, we also get rid of the CustomerId link statement. The result set would return 70 records, which is significantly more than the INNER JOIN statement. You’ll perceive a pattern in the way the records are returned. As with every customer, the order IDs are presented, and for every order ID, SQL tabulates a customer.

Like the INNER JOIN statement, you may also employ the WHERE clause with a CROSS JOIN statement. Let’s proceed by employing the WHERE filter for a CROSS JOIN statement that we would utilize with an INNER JOIN statement.

We understand that there is one customer with the ID of 6, and we grasp that we hold orders with IDs between 5 and 14, thus a total of 10 orders in the order table. While this kind of statement isn’t deployed frequently, there are occasions when you require deploying this kind of JOIN statement for reports and more intricate queries.

When To Use CROSS JOIN vs INNER JOIN

For nearly all parts, you’ll deploy an INNER JOIN much more than a CROSS JOIN. Conceive the rationale of the two statements. When a customer logs in to a web application and has to view orders, you would want to deploy the INNER JOIN statement.

Almost all people are curious to why you would ever deploy a CROSS JOIN. A CROSS JOIN is deployed when no easy INNER JOIN option is available. You may also be amazed that CROSS JOIN runs very smoothly. If you discover that an INNER JOIN becomes too complicated and slow, it might be time to start using a CROSS JOIN statement.

Generally, CROSS JOIN statements are utilized in reporting. Particularly, CROSS JOIN statements are deployed in reports for getting back averages and projections. They are chiefly deployed to bring back total amounts for every customer or order (as an example) analogous to the number of records in a particular table.

When you create applications or even merely SQL procedures, you’ll have to comprehend both of these statements. Even though the INNER JOIN is the most ordinary and accepted statement, if you’re liable for any reports, you’ll encounter a CROSS JOIN at some time during your career.

You Might Also Like

Back to top
%d bloggers like this: