SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL Data Types SQL OPERATORS SQL COMMANDS SQL Queries

SQL Database

SQL Create Database SQL DROP Database SQL SELECT Database

SQL Table

SQL TABLE SQL CREATE TABLE SQL COPY TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL DROP TABLE SQL UPDATE TABLE SQL INSERT TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT WHERE Clause SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT OR Operator SQL SELECT LIKE Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL WHERE Clause SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT Statement SQL INSERT INTO Statement SQL INSERT INTO SELECT

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL CROSS Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Cast Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL Aggregate Operators SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL SQL Formatter WEB SQL SQL Auto Increment Save Point in SQL space() function in SQL SQL Aggregate Functions SQL Topological Sorting SQL Injection SQL Cloning Tables SQL Aliases SQL Handling Duplicate

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL

SQL CROSS Join

In this tutorial, we will help you understand the concept of the SQL CROSS Join clause with the few examples.

The SQL CROSS Join query is used to join one or more than one table. The CROSS Join query performs the Cartesian product in the set of rows. The CROSS Join displays one row from the first table and joins all the rows from the other rows of the other tables. 

After executing the CROSS Join query, the number of rows in the result will equal the product of the number of rows in table one and the number of rows in table two.

For example:

Assume there are two tables, Table 1 and Table 2.

There are five rows in table 1 and six rows in table 2.

After executing the cross join query, the final result will get thirty records.

Cross Join = M * N

M = Number of rows in the first table.

N = Number of rows in the second table. 

The syntax of the SQL CROSS Join is as follows:

SELECT table1.column_name1, table1.columnname2, table1.column_name3, table1.columnname4, table2.column_name1, table2.column_name2, table2.column_name3, table2.column_name4 FROM table1 CROSS JOIN table2;

Let’s understand the SQL CROSS Join with the help of examples.

Assume the following table, which has certain data.

Table Name 1- D_Students

Student_IdStudent_NameFirst_ SemesterSecond_ SemesterThird_ SemesterFourth_ SemesterFifth_ SemesterSixth_ SemesterTotalDepartment_ Id
202111Vineeta Sharma938885859592905
202112Vaibhav Lokhande859092808582862
202113Yash Dhull908894878590893
202114Sonali Patole959092889290914
202115Amit Sonar858382869284851
202116Meena Mishra787580748577783
202117Mahesh Kumbhar758375788076825
202118Sakshi Patil807874788077832
202119Sopan Bhore706875758080802
202220Prajwal Lokhande808585757880864
202221Anuja Wanare858886828485854
202222Venkatesh Iyer908987909291903
202223Anushka Sen707571748078801
202224Aakash Jain807572748580832
202225Akshay Agarwal858378889082893
202226Shwetali Bhagwat908385889080861
202227Priya Wagh808385808285874
202228Saurabh Sangale858380908484895
202229Manthan Koli857584788280862
202230Mayur Jain808887909290881

Table Name 2- Department

Department_IdDepartment_Name
1Computer Engineering
2Information Technology
3Mechanical Engineering
4Automobile Engineering
5Civil Engineering
6Electrical Engineering

Example 1: Write a query to perform the cross joins on the D_students and Department table where the fifth_semester percentage is 82.

SELECT Student_Id, Student_Name, First_Semester, Second_Semester, Third_Semester, Fourth_Semester, Fifth_Semester, Sixth_Semester, Total, Department_Name FROM D_Students CROSS JOIN Department WHERE Fifth_Semester = 82;  

The above SQL CROSS Join query joins the D_Students table and department, displaying the records where the fifth_semester percentage is 82.

The output of the above example is as follows:

Student_IdStudent_NameFirst_ SemesterSecond_ SemesterThird_ SemesterFourth_ SemesterFifth_ SemesterSixth_ SemesterTotalDepartment_Name
202227Priya Wagh80838580828587Computer Engineering
202227Priya Wagh80838580828587Information Technology
202227Priya Wagh80838580828587Mechanical Engineering
202227Priya Wagh80838580828587Automobile Engineering
202227Priya Wagh80838580828587Civil Engineering
202227Priya Wagh80838580828587Electrical Engineering
202229Manthan Koli85758478828086Computer Engineering
202229Manthan Koli85758478828086Information Technology
202229Manthan Koli85758478828086Mechanical Engineering
202229Manthan Koli85758478828086Automobile Engineering
202229Manthan Koli85758478828086Civil Engineering
202229Manthan Koli85758478828086Electrical Engineering
SQL CROSS Join

Example 2: Write a query to perform the cross joins on the D_students and Department table where the Student_Name. Start with the letter ‘A’.

SELECT Student_Id, Student_Name, First_Semester, Second_Semester, Third_Semester, Fourth_Semester, Fifth_Semester, Sixth_Semester, Total, Department_Name FROM Department CROSS JOIN D_Students WHERE Student_Name LIKE 'A%';

The above SQL CROSS Join query joins the D_Students table and department, displaying the records where the Student_Name is. Start with the letter 'A'.

The output of the above example is as follows:

Student_IdStudent_NameFirst_ SemesterSecond_ SemesterThird_ SemesterFourth_ SemesterFifth_ SemesterSixth_ SemesterTotalDepartment_Name
202115Amit Sonar85838286928485Computer Engineering
202115Amit Sonar85838286928485Information Technology
202115Amit Sonar85838286928485Mechanical Engineering
202115Amit Sonar85838286928485Automobile Engineering
202115Amit Sonar85838286928485Civil Engineering
202115Amit Sonar85838286928485Electrical Engineering
202221Anuja Wanare85888682848585Computer Engineering
202221Anuja Wanare85888682848585Information Technology
202221Anuja Wanare85888682848585Mechanical Engineering
202221Anuja Wanare85888682848585Automobile Engineering
202221Anuja Wanare85888682848585Civil Engineering
202221Anuja Wanare85888682848585Electrical Engineering
202223Anushka Sen70757174807880Computer Engineering
202223Anushka Sen70757174807880Information Technology
202223Anushka Sen70757174807880Mechanical Engineering
202223Anushka Sen70757174807880Automobile Engineering
202223Anushka Sen70757174807880Civil Engineering
202223Anushka Sen70757174807880Electrical Engineering
202224Aakash Jain80757274858083Computer Engineering
202224Aakash Jain80757274858083Information Technology
202224Aakash Jain80757274858083Mechanical Engineering
202224Aakash Jain80757274858083Automobile Engineering
202224Aakash Jain80757274858083Civil Engineering
202224Aakash Jain80757274858083Electrical Engineering
202225Akshay Agarwal85837888908289Computer Engineering
202225Akshay Agarwal85837888908289Information Technology
202225Akshay Agarwal85837888908289Mechanical Engineering
202225Akshay Agarwal85837888908289Automobile Engineering
202225Akshay Agarwal85837888908289Civil Engineering
202225Akshay Agarwal85837888908289Electrical Engineering
SQL CROSS Join

Example 3: Write a query to perform the cross joins on the D_students and Department table where the second_semester > 85 and Sixth_Semester =90.

SELECT Student_Id, Student_Name, First_Semester, Second_Semester, Third_Semester, Fourth_Semester, Fifth_Semester, Sixth_Semester, Total, Department_Name FROM Department CROSS JOIN D_Students WHERE Second_Semester > 85 AND Sixth_Semester = 90;

The above SQL CROSS Join query joins the D_Students table and department, displaying the records where the second_semester > 85 and Sixth_Semester =90. The AND operator display those records where both the condition satisfy the criteria.

The output of the above example is as follows:

Student_IdStudent_NameFirst_ SemesterSecond_ SemesterThird_ SemesterFourth_ SemesterFifth_ SemesterSixth_ SemesterTotalDepartment_Name
202113Yash Dhull90889487859089Computer Engineering
202113Yash Dhull90889487859089Information Technology
202113Yash Dhull90889487859089Mechanical Engineering
202113Yash Dhull90889487859089Automobile Engineering
202113Yash Dhull90889487859089Civil Engineering
202113Yash Dhull90889487859089Electrical Engineering
202114Sonali Patole95909288929091Computer Engineering
202114Sonali Patole95909288929091Information Technology
202114Sonali Patole95909288929091Mechanical Engineering
202114Sonali Patole95909288929091Automobile Engineering
202114Sonali Patole95909288929091Civil Engineering
202114Sonali Patole95909288929091Electrical Engineering
202230Mayur Jain80888790929088Computer Engineering
202230Mayur Jain80888790929088Information Technology
202230Mayur Jain80888790929088Mechanical Engineering
202230Mayur Jain80888790929088Automobile Engineering
202230Mayur Jain80888790929088Civil Engineering
202230Mayur Jain80888790929088Electrical Engineering
SQL CROSS Join

Example 4: Write a query to perform the cross joins on the D_students and Department table where the third_semester includes 71, 72 percentages.

SELECT Student_Id, Student_Name, First_Semester, Second_Semester, Third_Semester, Fourth_Semester, Fifth_Semester, Sixth_Semester, Total, Department_Name FROM Department CROSS JOIN D_Students WHERE Third_Semester IN (71, 72);

The above SQL CROSS Join query joins the D_Students table and department, displaying the records where the third_semester includes 71 and 72 percentages.

The output of the above example is as follows:

Student_IdStudent_NameFirst_SemesterSecond_SemesterThird_SemesterFourth_SemesterFifth_SemesterSixth_SemesterTotalDepartment_Name
202223Anushka Sen70757174807880Computer Engineering
202223Anushka Sen70757174807880Information Technology
202223Anushka Sen70757174807880Mechanical Engineering
202223Anushka Sen70757174807880Automobile Engineering
202223Anushka Sen70757174807880Civil Engineering
202223Anushka Sen70757174807880Electrical Engineering
202224Aakash Jain80757274858083Computer Engineering
202224Aakash Jain80757274858083Information Technology
202224Aakash Jain80757274858083Mechanical Engineering
202224Aakash Jain80757274858083Automobile Engineering
202224Aakash Jain80757274858083Civil Engineering
202224Aakash Jain80757274858083Electrical Engineering
SQL CROSS Join

Example 4: Write a query to perform the cross joins on the D_students and Department table where the fifth_semester percentages are between 80 and 82.

SELECT Student_Id, Student_Name, First_Semester, Second_Semester, Third_Semester, Fourth_Semester, Fifth_Semester, Sixth_Semester, Total, Department_Name FROM Department CROSS JOIN D_Students WHERE Fifth_Semester BETWEEN 80 AND 82;

The above SQL CROSS Join query joins the D_Students table and department, displaying the records where the fifth_semester percentages are between 80 and 82.

The output of the above example is as follows:

Student_IdStudent_NameFirst_SemesterSecond_SemesterThird_SemesterFourth_SemesterFifth_SemesterSixth_SemesterTotalDepartment_Name
202117Mahesh Kumbhar75837578807682Computer Engineering
202117Mahesh Kumbhar75837578807682Information Technology
202117Mahesh Kumbhar75837578807682Mechanical Engineering
202117Mahesh Kumbhar75837578807682Automobile Engineering
202117Mahesh Kumbhar75837578807682Civil Engineering
202117Mahesh Kumbhar75837578807682Electrical Engineering
202118Sakshi Patil80787478807783Computer Engineering
202118Sakshi Patil80787478807783Information Technology
202118Sakshi Patil80787478807783Mechanical Engineering
202118Sakshi Patil80787478807783Automobile Engineering
202118Sakshi Patil80787478807783Civil Engineering
202118Sakshi Patil80787478807783Electrical Engineering
202119Sopan Bhore70687575808080Computer Engineering
202119Sopan Bhore70687575808080Information Technology
202119Sopan Bhore70687575808080Mechanical Engineering
202119Sopan Bhore70687575808080Automobile Engineering
202119Sopan Bhore70687575808080Civil Engineering
202119Sopan Bhore70687575808080Electrical Engineering
202223Anushka Sen70757174807880Computer Engineering
202223Anushka Sen70757174807880Information Technology
202223Anushka Sen70757174807880Mechanical Engineering
202223Anushka Sen70757174807880Automobile Engineering
202223Anushka Sen70757174807880Civil Engineering
202223Anushka Sen70757174807880Electrical Engineering
202227Priya Wagh80838580828587Computer Engineering
202227Priya Wagh80838580828587Information Technology
202227Priya Wagh80838580828587Mechanical Engineering
202227Priya Wagh80838580828587Automobile Engineering
202227Priya Wagh80838580828587Civil Engineering
202227Priya Wagh80838580828587Electrical Engineering
202229Manthan Koli85758478828086Computer Engineering
202229Manthan Koli85758478828086Information Technology
202229Manthan Koli85758478828086Mechanical Engineering
202229Manthan Koli85758478828086Automobile Engineering
202229Manthan Koli85758478828086Civil Engineering
202229Manthan Koli85758478828086Electrical Engineering
SQL CROSS Join



ADVERTISEMENT
ADVERTISEMENT