Join Our 4-Week Free Gen AI Course with select Programs.

Request a callback

or Chat with us on

What are SQL JOINs: Inner, Outer, and Cross JOINs with Examples

Basics of Python
Basics of Python
icon
5 Hrs. duration
icon
9 Modules
icon
1800+ Learners
logo
Start Learning

SQL, or Structured Query Language, is regarded as the core of relational databases, which provides aid in handling data. It offers extensive features, including Injection, Triggers, Joins, Hosting, etc.

 

Joins are one of the most important concepts of SQL. In this article, we will give you informative insights into SQL JOINs and also look into the different types of SQL Joins. Please keep reading until the end of the article to know more about SQL Joins in detail. Let’s begin!

 

Are you interested in full-stack development? You can check out Full Stack Development with Cloud for Web and Mobile!

What is SQL?

SQL is a programming language that is used for storing and processing information within a relational database. A relational database stores information in a tabular format representing different data attributes and relationships between the data values.

 

You can also use SQL statements for storing, updating, removing, searching, and retrieving information from the database. Besides, it can also be used for maintaining and optimising database performance.

 

Are you preparing yourself for SQL? If so, then go through these SQL-interview-questions.

What are JOINs in SQL?

JOINs in SQL are typical commands that combine rows from different tables based on related columns between those tables. Users predominantly use this command while extracting data from tables that share many-to-many relationships.

Importance of SQL JOINs

SQL JOINs enable you to combine data from tables based on related columns. The importance of SQL JOINs can be stated as follows:

 

  • Data integrity
  • Data retrieval
  • Optimised performance
  • Flexible data query
  • Business intelligence
  • Query optimisation, etc.

Different Types of SQL JOINs

The SQL server primarily supports four distinctive variants of JOINs. Each JOIN query in SQL defines how two or more tables are related within a query. 

Here we have enlisted the various types of SQL JOIN support within the SQL Server.

  • Inner Joins in SQL
  • Outer Joins in SQL
  • Cross Joins in SQL
  • Right Joins in SQL
  • Left Joins in SQL
  • Full-outer Joins in SQL

Inner JOINs in SQL

This type of JOIN in SQL returns all records from multiple tables that satisfy the specified JOIN condition. It is considered the simplest and the most popular form of JOIN in SQL that assumes a default JOIN. Omitting the INNER keyword from the JOIN query will fetch you a similar output.

 

Functions of Inner JOINs in SQL

This type of JOIN in SQL combines rows from two or more tables based on an entirely matching condition. It only returns the rows that have matching values in both tables.

 

Syntax and How to Use Them

SELECT columns FROM table1 INNER JOIN table2 ON condition1 INNER JOIN table3 ON condition2

Examples

Firstly, we will create two tables “Student” and “Fee” using the following statement: CREATE TABLE Student ( id int PRIMARY KEY IDENTITY, admission_no varchar(45) NOT NULL, first_name varchar(45) NOT NULL, last_name varchar(45) NOT NULL, age int, city varchar(25) NOT NULL ); CREATE TABLE Fee ( admission_no varchar(45) NOT NULL, course varchar(45) NOT NULL, amount_paid int, ); Now, we will incorporate a few records into these tables by using these statements: INSERT INTO Student (admission_no, first_name, last_name, age, city) VALUES (3354,'Luisa', 'Evans', 13, 'Texas'), (2135, 'Paul', 'Ward', 15, 'Alaska'), (4321, 'Peter', 'Bennett', 14, 'California'), (4213,'Carlos', 'Patterson', 17, 'New York'), (5112, 'Rose', 'Huges', 16, 'Florida'), (6113, 'Marielia', 'Simmons', 15, 'Arizona'), (7555,'Antonio', 'Butler', 14, 'New York'), (8345, 'Diego', 'Cox', 13, 'California'); INSERT INTO Fee (admission_no, course, amount_paid) VALUES (3354,'Java', 20000), (7555, 'Android', 22000), (4321, 'Python', 18000), (8345,'SQL', 15000), (5112, 'Machine Learning', 30000);

Next, you need to execute the SELECT statement for verifying the records:

 

Table: Student

 

ID Admission_No First_Name Last_Name Age City
1 3354 Luisa Evans 13 Texas
2 2135 Paul Ward 15 Alaska
3 4321 Peter Bennett 14 California
4 4213 Carlos Patterson 17 New York
5 5112 Rose Huges 16 Florida
6 6113 Marielia Simmons 15 Arizona
7 7555 Antonio Butler 14 New York
8 8345 Diego Cox 13 California

 

Table: Fee

 

Admission_no Course Amount_Paid
3345 Java 20000
7555 Android 22000
4321 Python 18000
8345 SQL 15000
5112 Machine Learning 30000

Best Practices and Common Pitfalls 

  • Using proper indexing

  • Using aliases for readability
  • Avoiding excessive tables, etc.

Some of the common pitfalls that you’re likely to encounter while using this JOIN in SQL are:

 

  • Incorrect JOIN conditions
  • Data skewness
  • Performance issues, etc.

Outer JOINs

Outer JOINs in SQL Server return all records from both tables that genuinely satisfies the JOIN condition. Simply put, thisSQL  JOIN will return the matching record and all the unmatched rows from all tables.

 

Explanation of Outer JOINs and Their Function

Outer JOINs in SQL return all rows from one table and matching rows from another. Furthermore, it also includes non-matching rows from one or both tables.

 

Syntax for Left, Right, and Full Outer SQL JOINs and How to Use Them

The following syntax illustrates the use of left outer JOIN in SQL Server:

 

SELECT column_lists FROM table1 LEFT [OUTER] JOIN table2 ON table1.column = table2.column; The following syntax illustrates the use of right outer JOIN in SQL Server: SELECT column_lists FROM table1 RIGHT [OUTER] JOIN table2 ON table1.column = table2.column; The following syntax illustrates the use of full outer JOIN in SQL Server: SELECT column_lists FROM table1 FULL [OUTER] JOIN table2 ON table1.column = table2.column;

Examples of Outer JOINs in Action

We can seamlessly demonstrate the LEFT OUTER JOIN using the following command:

 

SELECT Student.admission_no, Student.first_name, Student.last_name, Fee.course, Fee.amount_paid FROM Student LEFT OUTER JOIN Fee ON Student.admission_no = Fee.admission_no; Here’s howe you can use the RIGHT OUTER JOIN to get records from both tables: SELECT Student.admission_no, Student.first_name, Student.last_name, Fee.course, Fee.amount_paid FROM Student RIGHT OUTER JOIN Fee ON Student.admission_no = Fee.admission_no; This example clearly explains the techniques of using the FULL OUTER JOIN to get records from both tables: SELECT Student.admission_no, Student.first_name, Student.last_name, Fee.course, Fee.amount_paid FROM Student FULL OUTER JOIN Fee ON Student.admission_no = Fee.admission_no;

Best Practices and Common Pitfalls When Using Outer JOINs

Some of the best practices for using Outer JOINs include:

 

  • Using appropriate JOIN syntax
  • Defining the primary purpose of JOIN
  • Understanding the order of operations
  • Testing the results with sample data

Some of the most common pitfalls that you’re likely to encounter are:

 

  • Using inappropriate JOIN syntax
  • Understanding the differences between various types of outer JOINs
  • Not accounting for NULL values

Cross JOINs

The cross JOINs in SQL combine all the possibilities of different tables, returning results from each row of every contributing table. 

 

Explanation of Cross JOINs and Their Function

Cross JOIN is a join in SQL that produces a Cartesian product of two tables. It results in all possible combinations of the rows and doesn’t require any standard columns between the tables.

 

Syntax for Cross JOINs in SQL  and How to Use Them

SELECT column_lists FROM table1 CROSS JOIN table2;

Examples of Cross JOINs in Action

 

Here’s how we can demonstrate the CROSS JOIN with the following command:

SELECT Student.admission_no, Student.first_name, Student.last_name, Fee.course, Fee.amount_paid FROM Student CROSS JOIN Fee WHERE Student.admission_no = Fee.admission_no;

Best Practices and Common Pitfalls When Using Cross JOINs

The best practices for using cross JOINs include:

 

  • Using proper filtering only to limit the size of the result set, understanding data and the primary purpose of SQL JOIN
  • Testing all results with the help of sample data

Some of the common pitfalls that you might encounter are:

 

  • An extensive number of rows
  • Producing unintended combinations
  • Noticeable performance issues
DevOps & Cloud Engineering
Internship Assurance
DevOps & Cloud Engineering

Comparison of Different Types of JOINs in SQL

SQL JOIN Type Description
INNER JOIN Returns only the matching rows from both tables based on a specified condition
LEFT JOIN Returns all rows from the left table and matching rows from the right table, or NULL values if there is no match
RIGHT JOIN Returns all rows from the right table and matching rows from the left table, or NULL values if there is no match
FULL OUTER JOIN Returns all rows from both tables, including NULL values for non-matching rows
CROSS JOIN Returns the Cartesian product of both tables, producing all possible combinations of their rows

 

Check out What is Arrays in Java | Everything You Need to Know.

Pros and Cons of Each Type of JOINs in SQL

Here we have listed down the pros and cons of each type of JOIN query in SQL:

 

INNER JOIN:

Pros:

  • Returns only the matching rows, which can improve query performance.
  • Easy to understand and use.

Cons:

  • Can exclude non-matching rows that might be relevant to the query.

Check out more on Inner JOIN SQL.

 

LEFT JOIN

Pros:

  • Returns all rows from the left table, including non-matching rows.
  • Useful for finding all records in one table that match or don’t match records in another table.

Cons:

  • It can produce NULL values, which might require additional handling.
  • Slower than INNER JOIN due to the larger result set.

 

RIGHT JOIN

Pros:

  • Returns all rows from the right table, including non-matching rows.
  • Useful for finding all records in one table that match or don’t match records in another table.

Cons:

  • It can produce NULL values, which might require additional handling.
  • Slower than INNER JOIN due to the more significant result set.

 

FULL OUTER JOIN

Pros:

  • Returns all rows from both tables, including non-matching rows.
  • Useful for finding all records that exist in one or both tables.

Cons:

  • It can produce a substantial result set.
  • It can be slow due to the more significant result set.

 

CROSS JOIN

Pros:

  • It can help generate all possible combinations of rows from both tables.
  • Useful for testing and generating data sets.

Cons:

  • It can produce a huge result set.
  • Not suitable for most queries due to the large number of combinations generated.

You can learn more about the Advantages and Disadvantages of Arrays in C, C++ and Java!

Choosing the Right Type of SQL JOIN for Your Data Needs

Consider the relationship between the data and tables you wish to retrieve to pick out the right JOIN type. You can use the INNER JOIN to retrieve matching data and LEFT or RIGHT JOIN to include non-matching data. Besides, you can also use the FULL OUTER JOIN to include all data and CROSS JOIN to combine all possible combinations.

Conclusion

In this article we have what SQL joins and, the major types of joins in SQL. To sum up, SQL JOINs enable you to combine data from multiple tables. You must choose the appropriate SQL JOIN type based on the relationship between tables and data requirements.

 

Check out Why Developers love Python & JavaScript!

FAQs
The primary purpose of using JOINs in SQL is to access data from multiple tables according to their logical relationships. SQL JOINs are used to fetch data from database tables and represent the result dataset as a separate table.
There are various types of joins in SQL. The LEFT OUTER JOINs deliver faster and better performance in most cases.
If you wish to remove JOINs in SQL, you need to incorporate the technique of JOIN elimination. The SQL Server query optimiser mostly uses this unique technique to create efficient query plans. SQL JOIN elimination usually occurs when SQL Server can establish equality by using trusted database constraints to eliminate unnecessary JOINs in SQL.
You can easily use multiple JOIN statements at once to join more than a single table simultaneously. However, you must add a second INNER JOIN statement and a second ON statement. This will help in indicating the third table and also the second relationship.
You can use the keywords COUNT and GROUP BY to find duplicate records in SQL. This command will help you obtain all the duplicate records while using INNER JOIN along with COUNT and GROUP BY.
brochureimg

The DevOps Playbook

Simplify deployment with Docker containers.

Streamline development with modern practices.

Enhance efficiency with automated workflows.

left dot patternright dot pattern

Programs tailored for your success

Popular

Management

Data Science

Finance

Technology

Future Tech

Upskill with expert articles

View all
Hero Vired logo
Hero Vired is a leading LearnTech company dedicated to offering cutting-edge programs in collaboration with top-tier global institutions. As part of the esteemed Hero Group, we are committed to revolutionizing the skill development landscape in India. Our programs, delivered by industry experts, are designed to empower professionals and students with the skills they need to thrive in today’s competitive job market.

Data Science

Accelerator Program in Business Analytics & Data Science

Integrated Program in Data Science, AI and ML

Accelerator Program in AI and Machine Learning

Advanced Certification Program in Data Science & Analytics

Technology

Certificate Program in Full Stack Development with Specialization for Web and Mobile

Certificate Program in DevOps and Cloud Engineering

Certificate Program in Application Development

Certificate Program in Cybersecurity Essentials & Risk Assessment

Finance

Integrated Program in Finance and Financial Technologies

Certificate Program in Financial Analysis, Valuation and Risk Management

Management

Certificate Program in Strategic Management and Business Essentials

Executive Program in Product Management

Certificate Program in Product Management

Certificate Program in Technology-enabled Sales

Future Tech

Certificate Program in Gaming & Esports

Certificate Program in Extended Reality (VR+AR)

Professional Diploma in UX Design

Blogs
Reviews
Events
In the News
About Us
Contact us
Learning Hub
18003093939     ·     hello@herovired.com     ·    Whatsapp
Privacy policy and Terms of use

© 2024 Hero Vired. All rights reserved