- 4 years ago
- Zaid Bin Khalid
- 2512 Views
-
4
In this session, you will successfully learn the basics of how to fetch data from two tables by using SQL cross join.
Cross Join Operations.
The thumb rule for producing Sql Join Operation results can be derived by multiplying the number of rows in the first table and second table. You may also term cross join as Cartesian Product.
Make sure you do not use the WHERE clause while using Cross Join. However, if you are using WHERE clause and cross Join together then, it might function like Inner Join Operation.
The alternative way through which one might achieve the same output is to use column names that are separated through commas after you use Select statement and mention the name of the table that is involved after From clause.
CROSS JOIN.
We can specify cross join in two ways that includes.
- Using the JOIN syntax.
- The table in the FROM clause without using a WHERE clause.
Pictorial Representation of Cross Join.
Below is the pictorial illustration determining the working of Cross Join:
Typically, the cross join is the product of the number of rows used in each table.
Example of Using Cross Joins.
Here is the simplest example of using Cross Join Operation.
SELECT t1.emp_id, t1.emp_name, t1.hire_date, t2.dept_name
FROM employees AS t1 CROSS JOIN departments AS t2;
NOTE.
Cross Join Create a Cartesian product or Multiplication of all rows in one table with all rows in another. For example, if one table has five rows and another has ten rows, a cross join query will produce 50 rows, as the product result of five and ten rows.
Output Results – Using Cross Joins.
After executing the above SQL query, you will get the following result set.
+--------+--------------+------------+------------------+
| emp_id | emp_name | hire_date | dept_name |
+--------+--------------+------------+------------------+
| 1 | Ethan Hunt | 2001-05-01 | Administration |
| 2 | Tony Montana | 2002-07-15 | Administration |
| 3 | Sarah Connor | 2005-10-18 | Administration |
| 4 | Rick Deckard | 2007-01-03 | Administration |
| 5 | Martin Blank | 2008-06-24 | Administration |
| 1 | Ethan Hunt | 2001-05-01 | Customer Service |
| 2 | Tony Montana | 2002-07-15 | Customer Service |
| 3 | Sarah Connor | 2005-10-18 | Customer Service |
| 4 | Rick Deckard | 2007-01-03 | Customer Service |
| 5 | Martin Blank | 2008-06-24 | Customer Service |
| 1 | Ethan Hunt | 2001-05-01 | Finance |
| 2 | Tony Montana | 2002-07-15 | Finance |
| 3 | Sarah Connor | 2005-10-18 | Finance |
| 4 | Rick Deckard | 2007-01-03 | Finance |
| 5 | Martin Blank | 2008-06-24 | Finance |
| 1 | Ethan Hunt | 2001-05-01 | Human Resources |
| 2 | Tony Montana | 2002-07-15 | Human Resources |
| 3 | Sarah Connor | 2005-10-18 | Human Resources |
| 4 | Rick Deckard | 2007-01-03 | Human Resources |
| 5 | Martin Blank | 2008-06-24 | Human Resources |
| 1 | Ethan Hunt | 2001-05-01 | Sales |
| 2 | Tony Montana | 2002-07-15 | Sales |
| 3 | Sarah Connor | 2005-10-18 | Sales |
| 4 | Rick Deckard | 2007-01-03 | Sales |
| 5 | Martin Blank | 2008-06-24 | Sales |
+--------+--------------+------------+------------------+
Alternative of Using Cross Joins.
In some database systems such as PostgreSQL and Oracle, you can use the INNER JOIN clause with the condition that always evaluates to right to perform a cross join.
Discussion.
As you can see and check, it is not very useful like others join that we have learned in the previous session.
Hence, From employees’ table’s rows, that can can combine with each row from the departments’ table, the cross join query do not define a join operation condition.
Therefore, do not use a cross join in the table, unless you are confirmed that you want a Cartesian product in your table.
- 4 years ago
- Zaid Bin Khalid
- 2512 Views
-
4