What is SQL Server Cross Join?

SQL Server is a powerful and widely-used relational database management system (RDBMS) that is used to store and manage data in a structured format. One of the most important operations that you can perform in an SQL Server is joining data from multiple tables.

Joining tables allows you to combine data from multiple tables based on a related column between them. There are several types of joins available in SQL Server, each with its own behavior and use case.

In this SQL server tutorial, we will be discussing one of the types of join operations called SQL Server CROSS JOIN. We will be discussing in detail the CROSS JOIN operation, its use cases, how it works, and examples of how to use it in SQL Server.

• What is SQL Server CROSS JOIN condition?
• How does SQL Server CROSS JOIN work?
• What is the difference between the INNER JOIN condition and CROSS APPLY operator in SQL Server?
• What is the difference between the CROSS JOIN and FULL OUTER JOIN conditions on tables in SQL Server?
• How to use the SQL Server CROSS JOIN conditions on multiple tables
• What is the SQL Server CROSS JOIN table-valued function
• What is SQL server CROSS JOIN performance

What is SQL Server CROSS JOIN condition?

A SQL Server CROSS JOIN, also known as a Cartesian product, is a type of join operation in SQL Server that returns the combination of every row from the first table with every row from the second table. In other words, it returns the Cartesian product of the two tables.

• The result of a SQL Server CROSS JOIN can be thought of as a virtual table that contains all possible combinations of rows from the two tables.

The syntax is given below.

SELECT * FROM table1
CROSS JOIN table2

In this syntax, the query will return all columns from both table1 and table2, with each row of table1 being combined with each row of table2. The result set will have n * m number of rows, where n is the number of rows in table1 and m is the number of rows in table2.

Let’s take an example by following the below steps:

First, create two tables of customers and orders using the below command.

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(255) NOT NULL,
    country VARCHAR(255) NOT NULL
);

INSERT INTO customers (customer_id, first_name, last_name, email, phone, country)
VALUES (1, 'John', 'Doe', 'johndoe@gmail.com', '555-555-5555', 'USA'),
       (2, 'Jane', 'Doe', 'janedoe@gmail.com', '555-555-5556', 'USA'),
       (3, 'Bob', 'Smith', 'bobsmith@gmail.com', '555-555-5557', 'United Kingdom'),
	   (4, 'James', 'Doe', 'jamesdoe@gmail.com', '555-555-5559', 'USA');


CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    product_name VARCHAR(255) NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    date_ordered DATE NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

INSERT INTO orders (order_id, customer_id, product_name, quantity, price, date_ordered)
VALUES (1, 1, 'Computer', 1, 999.99, '2023-01-01'),
       (2, 2, 'Monitor', 2, 399.99, '2023-01-02'),
       (3, 2, 'Keyboard', 3, 49.99, '2023-01-03'),
       (4, 3, 'Mouse', 4, 29.99, '2023-01-04');

Fetch all the rows of both tables using the SQL Server CROSS JOIN.

SELECT * FROM customers
CROSS JOIN orders

The above query will return a result set that has every combination of customers and orders. For example, if there are 4 customers and 4 orders in the database, the query will return 16 rows (4 customers * 4 orders).

It is important to note that a CROSS JOIN in SQL Server is generally not recommended in practice as it can produce an enormous number of rows, depending on the size of the tables being joined. It can cause performance issues or cause the query to not return results at all due to a large number of rows.

Read: If Else In Trigger SQL Server

How does SQL Server CROSS JOIN work?

A CROSS JOIN in SQL Server is a Cartesian product, a sort of join operation that combines each record in the first table with each row in the second table and provides the result.

• A more precise description would be that it gives back the result of multiplying the two tables together in the Cartesian style. As a result of a CROSS JOIN, a virtual table is created that contains every conceivable combination of data from the two tables.
• The CROSS JOIN function combines data from several tables without having a comparison operator or condition like INNER JOIN or OUTER JOIN. It merely joins together each row in the first table with each row in the second.

You can use the CROSS JOIN keyword to perform the CROSS JOIN operation in SQL Server by inserting it between the tables that you want to join together. The following is an illustration of the CROSS JOIN operation, which allows you to join the contents of two tables.

SELECT customers.first_name, customers.last_name, orders.order_date, orders.product_name
FROM customers
CROSS JOIN orders

The above query will return all possible combinations of rows from the customers and orders tables. Each row from the customers table will be combined with every row from the orders table.

Read: Recursive Trigger in SQL Server

How different is the INNER JOIN from CROSS APPLY

1. SQL Server INNER Join

In SQL Server, both the INNER JOIN condition and the CROSS APPLY operator are used to combine data from multiple tables. However, they are used in different situations and have slightly different behaviors.

• An SQL Server INNER JOIN is used to combine rows from two or more tables based on a related column between them.
• The result of an INNER JOIN will only include rows where there is a match between the specified columns on both tables. The INNER JOIN is used to return only the rows that have matching values in both tables.

The syntax is given below.

SELECT column1, column2, ...
FROM table1
INNER JOIN table2 ON table1.column = table2.column;

In this syntax, the query will return all columns specified in the SELECT statement, with each row of table1 being combined with the matching row of table2 based on the equality between table1.column and table2.column.

Let’s take an example using the below query.

SELECT customers.first_name, customers.last_name, orders.product_name, orders.quantity
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id;

The above query uses an INNER JOIN to combine rows from the customers table with rows from the orders table. The result will include only the rows where the customer_id column in the customers table matches the customer_id column in the orders table. In other words, it will return all the orders that have a customer associated with it.

1. SQL Server CROSS APPLY

On the other hand, the SQL Server CROSS APPLY operator is used to combine a table with a table-valued function. A table-valued function is a function that returns a table as its output.

• The CROSS APPLY operator is used to join a table with the output of a table-valued function, and it will return all rows from the table, combined with the matching rows from the output of the table-valued function.

The syntax is given below.

SELECT column1, column2, ...
FROM table1
CROSS APPLY table_valued_function(table1.column);

In this syntax, the query will return all columns specified in the SELECT statement, with each row of table1 being combined with the matching rows from the output of the table-valued function table_valued_function, based on the input of table1.column.

Let’s take an example by following the below steps:

First, create the table-valued function named get_order_total using the below code.

CREATE FUNCTION dbo.get_order_total (@customer_id INT)
RETURNS TABLE
AS
RETURN
    SELECT SUM(price * quantity) AS total_amount
    FROM orders
    WHERE customer_id = @customer_id;

The above code creates a new table-valued function named get_order_total in the dbo schema of the current database.

This function takes a single parameter, @customer_id, and returns a table with a single column total_amount that contains the total amount of orders for the customer specified by the @customer_id parameter.

In the above query you provided, dbo refers to the database owner (short for “database owner”) in SQL Server. In SQL Server, each database has one owner, and this owner is the user who is able to perform all operations on the database.

The owner is also known as the schema owner, and the default schema for the owner is “dbo” (short for “database owner”).

When you see dbo.object_name in a SQL statement, it refers to an object (such as a table, view, or stored procedure) that is owned by the dbo schema.

Execute the below query with the CROSS APPLY operator to get the total order.

SELECT customers.first_name, customers.last_name, order_total.total_amount
FROM customers
CROSS APPLY dbo.get_order_total(customers.customer_id) order_total

The above query uses a CROSS APPLY to combine rows from the customers table with the output of a table-valued function named get_order_total. The function takes a single parameter, the customer_id, and returns a table with a single column total_amount.

The result will include all rows from the customers table, combined with the matching rows from the output of the get_order_total function based on the customer_id input.

As you can see, the main difference between the INNER JOIN and CROSS APPLY is that INNER JOIN combines rows from two or more tables based on related columns between them, while CROSS APPLY combines a table with the output of a table-valued function.

It’s worth noting that CROSS APPLY can also be used with a subquery, it works in the same way as a table-valued function, the main difference is that the subquery is defined in the query instead of being a separate object.

Read: SQL Server Trigger to Increment Id

What’s the difference between SQL Server tables’ CROSS JOIN and FULL OUTER JOIN conditions

In SQL Server, both the CROSS JOIN and the FULL OUTER JOIN are used to combine data from multiple tables, but they have different behaviors and are used in different situations.

• A SQL Server CROSS JOIN, also known as a Cartesian product, returns the combination of every row from the first table with every row from the second table. The result of a CROSS JOIN can be thought of as a virtual table that contains all possible combinations of rows from the two tables.
• The CROSS JOIN does not use any comparison operator or condition to combine the rows, it simply combines every row from the first table with every row from the second table.

The syntax for a SQL Server CROSS JOIN is very simple and straightforward, it is just adding the CROSS JOIN keyword between the tables you want to join.

SELECT * FROM table1
CROSS JOIN table2

Here we will not talk about CROSS JOIN because we have already discussed it in the above subsection.

SQL Server FULL OUTER JOIN

On the other hand, a FULL OUTER JOIN is used to combine rows from two tables based on a related column between them, and it returns all rows from both tables, including the unmatched rows.

• The FULL OUTER JOIN returns all rows from the left table and all rows from the right table and matches between the two. The unmatched rows will contain NULL values in the columns from the table that does not have a match.

The syntax is given below.

SELECT column1, column2, ...
FROM table1
FULL OUTER JOIN table2
ON table1.column = table2.column;

Where,

• SELECT column1, column2, …: This specifies the columns that you want to include in the result set. You can specify one or more columns from one or both tables.
• FROM table1: This specifies the left table in the join.
• FULL OUTER JOIN table2: This specifies that you are performing a full outer join operation on the two tables, table1, and table2.
• ON table1.column = table2.column: This is the join condition that specifies how the two tables are related. It defines the column that will be used to match the rows from the two tables.

The result of a FULL OUTER JOIN will include all rows from both tables, including the unmatched rows. The unmatched rows will contain NULL values in the columns from the table that does not have a match.

Let’s take an example using the below code.

SELECT customers.first_name, customers.last_name, orders.product_name
FROM customers
FULL OUTER JOIN orders
ON customers.customer_id = orders.customer_id

In the above example, the query combines data from the customers table and the orders table. The join condition ON customers.customer_id = orders.customer_id specifies that the two tables are related based on the customer_id column. The result set includes all columns specified in the SELECT statement.

The query will return all customers and all their orders, if any. If a customer does not have any order, the order columns will have null values. Also, if an order does not have a customer, the customer columns will have null values.

SQL Server CROSS JOIN for multiple tables

We have already covered “What is SQL Server CROSS JOIN” in the first section of this tutorial. So here we will discuss how to use the CROSS JOIN with multiple tables.

• When working with multiple tables, the CROSS JOIN can be used to combine the rows from all the tables. To use the CROSS JOIN on multiple tables, you can chain multiple CROSS JOIN conditions together.

The syntax is given below.

SELECT column1, column2, ...
FROM table1
CROSS JOIN table2
CROSS JOIN table3
...

In the above syntax, you can chain multiple SQL Server CROSS JOIN conditions together to combine data from multiple tables. Each CROSS JOIN operation combines the rows from the previous join result with the rows from the next table.

You can specify one or more columns from one or more tables in the SELECT statement, and use the CROSS JOIN operation between them.

Let’s take an example by following the below steps:

Create a table called suppliers using the below code.

CREATE TABLE suppliers (
    supplier_id INT PRIMARY KEY,
    company_name VARCHAR(255) NOT NULL,
    contact_name VARCHAR(255),
    contact_title VARCHAR(255),
    address VARCHAR(255),
    city VARCHAR(255),
    region VARCHAR(255),
    postal_code VARCHAR(255),
    country VARCHAR(255),
    phone VARCHAR(255),
    fax VARCHAR(255),
    website VARCHAR(255)
);

INSERT INTO suppliers (supplier_id, company_name, contact_name, contact_title, address, city, region, postal_code, country, phone, fax, website)
VALUES (1, 'Exotic Liquids', 'Charlotte Cooper', 'Purchasing Manager', '49 Gilbert St.', 'London', NULL, 'EC1 4SD', 'UK', '(171) 555-2222', NULL, 'http://www.exoticliquids.co.uk'),
       (2, 'New Orleans Cajun Delights', 'Shelley Burke', 'Order Administrator', 'P.O. Box 78934', 'New Orleans', 'LA', '70117', 'USA', '(100) 555-4822', '(100) 555-3392', 'http://www.neworleanscajundelights.com'),
       (3, 'Grandma Kelly''s Homestead', 'Regina Murphy', 'Sales Representative', '707 Oxford Rd.', 'Ann Arbor', 'MI', '48104', 'USA', '(313) 555-5735', '(313) 555-3349', 'http://www.grandmakellyshomestead.com');

Now use the CROSS JOIN to combine data from three tables of customers, orders, and suppliers in SQL Server.

SELECT customers.first_name, customers.last_name, orders.date_ordered, suppliers.company_name
FROM customers
CROSS JOIN orders
CROSS JOIN suppliers

The above query will return all possible combinations of rows from the customers, orders, and suppliers tables. Each row from the customers table will be combined with every row from the orders table and every row from the suppliers table.

Read: RIGHT JOIN in SQL Server

SQL Server CROSS JOIN table-valued function

A Table-Valued Function (TVF) in SQL Server is a type of user-defined function that returns a table as its output. It is similar to a stored procedure, but it can be used in SELECT statements just like a table.

A CROSS JOIN table-valued function is a special type of TVF that performs a CROSS JOIN operation on two or more tables and returns the result set as a table. This allows you to perform a CROSS JOIN operation and use the result set in the same way as a table.

The syntax is given below.

CREATE FUNCTION CrossJoinTables (@table1Name sysname, @table2Name sysname)
RETURNS TABLE
AS
RETURN
(
    SELECT *
    FROM @table1Name
    CROSS JOIN @table2Name
)

Where,

• CREATE FUNCTION CrossJoinTables (@table1Name sysname, @table2Name sysname): This line creates a new function named CrossJoinTables that takes two parameters, @table1Name and @table2Name, both of which are of type sysname. These parameters are used to specify the names of the tables that you want to cross-join.
• RETURNS TABLE: This line specifies that the function returns a table as its output.
• AS: This line is used to separate the function header from the function body.
• RETURN ( SELECT * FROM @table1Name CROSS JOIN @table2Name): This is the function body, it performs a CROSS JOIN operation on the two tables passed as parameters, using the SELECT statement and the CROSS JOIN keywords. The SELECT statement retrieves all columns from the tables.
• The SELECT statement is enclosed in parentheses and preceded by the RETURN keyword, which specifies that the SELECT statement’s result set is returned as the output of the function.

The above function takes two parameters, the names of the tables that you want to CROSS JOIN, and returns the result set as a table. You can then use this function in a SELECT statement just like a table.

Using SQL Server CROSS JOIN table-valued functions can be a useful way to encapsulate complex join logic and make it reusable throughout your application. It can also be useful for situations where you need to perform a CROSS JOIN operation on multiple tables and use the result set in multiple places in your application.

Read: How to execute Trigger in SQL Server

What is SQL server CROSS JOIN performance

The CROSS JOIN in SQL Server can be a powerful tool for combining data from multiple tables, but it can also have a significant impact on performance. This is because a CROSS JOIN generates a Cartesian product of the two tables, resulting in a large number of rows, which can cause the query to run slowly and consume a lot of resources.

• When working with large tables, a CROSS JOIN can quickly generate a huge number of rows, even if only a small percentage of those rows are needed for the final result. This can lead to a significant increase in the amount of data that needs to be processed, which can cause the query to run much slower and consume more resources.
• To avoid performance issues, it’s important to only use the CROSS JOIN operation when it’s necessary. If you only need to combine a small subset of the rows from each table, it’s better to use a more specific type of join, such as an INNER JOIN or an OUTER JOIN. These types of joins only return the rows that match a specific condition, which can greatly reduce the number of rows that need to be processed.
• It’s also important to be mindful of the number of tables that you’re joining, as chaining multiple CROSS JOIN operations together can quickly result in a large number of rows. If you need to join multiple tables, it’s better to use a more efficient join method, such as the INNER JOIN or OUTER JOIN, or to use subqueries to join the tables in stages.

• Another way to improve the performance is to use indexes, indexes can improve the query performance by reducing the number of rows that need to be scanned. Also, it’s important to use the appropriate data types and validate the data before storing it in the table.

In general, when working with the CROSS JOIN, it’s important to be aware of the potential performance issues and to use it only when it’s necessary. And when it’s necessary, try to optimize the query as much as possible.

Conclusion

In conclusion, the CROSS JOIN operation in SQL Server is a powerful tool that allows you to combine data from multiple tables in a very specific way. By returning the Cartesian product of the two tables, it creates a virtual table that contains all possible combinations of rows from the two tables.

You may also like to read the following SQL Server tutorials.

• Delete From With Join in SQL Server
• SQL Server LEFT JOIN Tutorial
• SQL Server Create Trigger If Not Exists
• SQL Server Trigger After Insert Update
• How to get trigger definition in SQL Server