EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 600+ Courses All in One Bundle
  • Login
Home Software Development Software Development Tutorials MariaDB Tutorial MariaDB Join
Secondary Sidebar
MariaDB Tutorial
  • MariaDB
    • MariaDB Versions
    • MariaDB? list users
    • MariaDB Commands
    • MariaDB odbc
    • MariaDB Workbench
    • MariaDB for windows
    • MariaDB Server
    • MariaDB? Data Types
    • MariaDB? boolean
    • MariaDB phpMyAdmin
    • MariaDB Mysqldump
    • MariaDB Java Connector
    • MariaDB insert
    • MariaDB UPDATE
    • MariaDB? rename column
    • MariaDB AUTO_INCREMENT
    • MariaDB Timezone
    • MariaDB GROUP_CONCAT
    • MariaDB wait_timeout
    • MariaDB MaxScale
    • MariaDB? with
    • MariaDB GUI
    • MariaDB? create?table
    • MariaDB? SHOW TABLES
    • MariaDB alter table
    • MariaDB List Tables
    • MariaDB JSON Functions
    • MariaDB Foreign Key
    • MariaDB? trigger
    • MariaDB Grant All Privileges
    • MariaDB Select Database
    • MariaDB? create database
    • MariaDB Delete Database
    • MariaDB Join
    • MariaDB JSON
    • MariaDB? show databases
    • MariaDB List Databases
    • MariaDB Functions
    • MariaDB? TIMESTAMP
    • MariaDB create user
    • MariaDB add user
    • MariaDB Max Connections
    • MariaDB show users
    • MariaDB Delete User
    • MariaDB? change user password
    • MariaDB? change root password
    • MariaDB reset root password
    • MariaDB IF
    • MariaDB bind-address
    • MariaDB Transaction
    • MariaDB Cluster
    • MariaDB Logs
    • MariaDB Encryption
    • MariaDB? backup
    • MariaDB Replication
    • MariaDB max_allowed_packet
    • MariaDB? performance tuning
    • MariaDB export database
    • MariaDB? import SQL

MariaDB Join

MariaDB Join

Introduction to MariaDB Join

MariaDB provides join functionality to the user. Basically, join is used to retrieve data from multiple tables. When we have more than one table, and we need to display a combined attribute at that time, we can use join constraint, or we can say join functionality. Join operation is based on a matching column that we call keys; MariaDB provides different types of join constraint such as simple join, left join and right join. When we need to implement join constraint at that time, we must know basic about key constraint then and then we can use join constraint as per our requirement.

Types of Join in MariaDB

Given below are the three different types of MariaDB join:

1. MariaDB Inner Join

MariaDB inner join is also called simple join. In which we can show all records from either table or multiple tables when conditions are satisfied.

Syntax:

Start Your Free Software Development Course

Web development, programming languages, Software testing & others

select colm name 1, colm name 2,…… colm name N
from table name 1
inner join table name 2
on table name 1.colm name = table name 2. Colm name;

Explanation:

  • In the above syntax, we use a select clause with an attribute of table name 1; after that, we use inner join with table name 2; the inner join keyword is used to retrieve data from both tables.
  • Then we use another keyword that is on with the colm name of table 1 and colm name of table 2. This syntax is executed when the condition is matched.

Example:

We need two different tables for implementation purposes, so first, create two tables by using the following statement.

Code:

create table stud(
stud_id int auto_increment,
stud_name varchar(255) not null,
address varchar(255) not null,
primary key (stud_id));

So first, we created a stud table with different attributes then insert some records by using insert into a statement as follows.

Code:

insert into stud(stud_name, address) values ("John", "Mumbai"), ("Jenny", "Goa"), ("Sam", "Mumbai");
select * from stud;

Now the first table is ready, to create another table and insert some records by using the same process.

Code:

create table empp(
emp_id int auto_increment,
emp_dept varchar(255) not null,
salary varchar(255) not null,
primary key (emp_id));

Now insert some records by using insert into the statement as follows.

All in One Software Development Bundle(600+ Courses, 50+ projects)
Python TutorialC SharpJavaJavaScript
C Plus PlusSoftware TestingSQLKali Linux
Price
View Courses
600+ Online Courses | 50+ projects | 3000+ Hours | Verifiable Certificates | Lifetime Access
4.6 (86,328 ratings)

Code:

insert into empp(emp_dept, salary) values ("Comp", 10000), ("Mech", 15000), ("Account", 25000), ("HR", 35000), ("Transport",12000);
select * from empp;

Now both tables are ready. The result of the above statement we illustrate by using the following snapshot.

Output:

stud table:

MariaDB Join 1

empp table:

MariaDB Join 2

Now we can implement inner join with the help of the above two tables and syntax as follows.

Code:

select stud_id, stud_name, salary from stud
inner join empp
on stud.stud_id = empp.emp_id;

Explanation:

  • Sometimes users want to see records from multiple tables. At that time, we use join here; we use an inner join to show a combined view of both tables.
  • In this example, we use a select clause with column name from the first table as well as the second table as shown in the table; after that, we use inner join and on a keyword with conditions as shown in the above statement.
  • The result of the above statement we illustrate by using the following snapshot.

Output:

MariaDB Join 3

Now we can compare the above screenshot for more details.

2. MariaDB Left Join

MariaDB Left Join is also called a left outer join. In which we return all rows from the left side table specified in the on condition and only those rows from the right table that match the condition.

Syntax:

select colm name 1, colm name 2,…… colm name N
from table name 1
left [outer] join table name 2
on table name 1.colm name = table name 2. Colm name;

Explanation:

  • In the above syntax, we use a select clause with join keywords such as left join and on; it works the same as an inner join.
  • In the left join, it returns all rows from the left side table if condition is met.

Example:

We have two table stud and empp that were previously created; here, we assume stud is the left side table, and empp is the right side table.

Code:

select stud_id, stud_name, emp_dept from stud
left join empp
on stud.stud_id = empp.emp_id;

Explanation:

  • In the above example, we use a select clause with different attributes, as shown in the statement.
  • Here we use the left join type of MariaDB join in which that left join returns all rows from the left side table, and the only condition matches rows from the right side table; in this example, our left side table is a stud.
  • The result of the above statement we illustrate by using the following snapshot.

Output:

Left

3. MariaDB Right Join

This is another type of MariaDB join; it is also called MariaDB Right Outer Join. Right works the same as left join. In the right join, it returns all rows from the right-hand side table and only matching rows from the left side table.

Syntax:

select colm name 1, colm name 2,…… colm name N
from table name 1
right [outer] join table name 2
on table name 1.colm name = table name 2. Colm name;

Explanation:

  • In the above syntax, we use the select clause the same as previous types. The only difference is that we use the right join keyword with the left side table and right side table.

Example:

Code:

select stud_id, stud_name, emp_dept, salary from stud
right join empp
on stud.stud_id = empp.emp_id;

Explanation:

  • In the above example, we use a select clause with a combined attribute from both tables that we need to show; after that, we use the right join keyword with the right side table, and for condition purpose, we use on keyword as shown in the above statement.
  • See here in the left side table there are 3 rows, and on the right side table, five rows are present; when we execute the above query, it returns all rows from the right-hand side and only matching rows from the left-hand side.
  • The result of the above statement we illustrate by using the following snapshot.

Output:

Right

Conclusion

From the above article, we saw types of Join with their basic syntax of each type, and we also see different examples of MariaDB Join. From this article, we saw how and when we use MariaDB Join.

Recommended Articles

This is a guide to MariaDB Join. Here we discuss the introduction and types of join in MariaDB for a better understanding. You may also have a look at the following articles to learn more –

  1. MariaDB Foreign Key
  2. Database Management Software
  3. Database Security
  4. Metadata Management Tools
Popular Course in this category
SQL Training Program (7 Courses, 8+ Projects)
  7 Online Courses |  8 Hands-on Projects |  73+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Java Tutorials
  • Python Tutorials
  • All Tutorials
Certification Courses
  • All Courses
  • Software Development Course - All in One Bundle
  • Become a Python Developer
  • Java Course
  • Become a Selenium Automation Tester
  • Become an IoT Developer
  • ASP.NET Course
  • VB.NET Course
  • PHP Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Software Development Course

C# Programming, Conditional Constructs, Loops, Arrays, OOPS Concept

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Software Development Course

Web development, programming languages, Software testing & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more