MySQL Tutorial

MySQL Tutorial MySQL Features MySQL Database Introduction MySQL Environmental Setup MySQL Data Types MySQL variable MySQL Advance table Query MySQL database queries MySQL Entity-Relationship Model MySQL Table Query MySQL Operators MySQL logical conditions MySQL Queries MySQL Clauses Clustered vs Non-Clustered Index MySQL Full text index MySQL Descending Index MySQL Invisible Index MySQL Composite Index MySQL Prefix index MySQL Index MySQL Create index MySQL Drop Index MySQL Show index MySQL Unique index MySQL Table MySQL Variable MySQL View MySQL Constraints MySQL Command Line Client Basic Queries MySQL Stored Procedure MySQL IF Statement MySQL Subquery MySQL Triggers

MySQL Join

MySQL Join MySQL CROSS JOIN MySQL DELETE JOIN MySQL EQUI JOIN MySQL INNER JOIN MySQL Union MySQL NATURAL JOIN MySQL RIGHT JOIN MySQL SELF JOIN MySQL UPDATE JOIN

MySQL Function

MySQL Function MySQL AVG() Function MySQL SUM() Function MySQL String() Function MySQL Advance() Function MySQL Aggregate() Function MySQL COALESCE() Function MySQL Control Flow Function MySQL COUNT() Function MySQL Date And Time Function MySQL GREATEST() Function MySQL ISNULL() Function MySQL LEAST() Function MySQL Math() Function MySQL MAX() Function MySQL MIN() Function MySQL find_in_set() function MySQL ASIN() Function MySQL CEIL() function MySQL CEILING() function MySQL TAN() Function MySQL Truncate() Function MySQL FLOOR() function MySQL LN() function MySQL LOG2() function MySQL LOG10() function MySQL MOD() function MySQL PI() function MySQL POW() function MySQL RADIANS() function MySQL RAND() function MySQL ROUND() function MySQL Character Length Function MySQL Current Date Function MySQL Date Add Function MySQL Date Format Function MySQL Datediff Function MySQL Day Function MySQL Elt Function MySQL Export Set Function MySQL Field Function MySQL Format Function MySQL From Base64 Function MySQL Hex Function MySQL Insert Function MySQL Instr Function MySQL Length Function MySQL CONCAT() function MySQL FIND_IN_SET() function MySQL LIKE() function MySQL LOAD_FILE() function MySQL LOCATE() function MySQL LOG() function MySQL MONTHNAME() function MySQL NOW() function MySQL PERIOD_ADD() function MySQL PERIOD_DIFF() function MySQL POWER() function MySQL QUARTER() function MySQL REVERSE() function MySQL RIGHT() Function MySQL RPAD() function MySQL RTRIM() function MySQL SEC_TO_TIME() function MySQL SOUNDEX() function

Questions

Which Clause is Similar to Having Clause in MySQL

Misc

MySQL Error 1046 - No Database Selected Failed to Start MySQL Service Unit MySQL Service Unit not Found Import MySQL Connector Mudule not Found Error No Module Named MySQL Joins Available in MySQL MySQL Docs MySQL Download For Windows 7 64 Bit MySQL Error Code 1064 MySQL Export MySQL History MySQL Host MySQL Import MySQL Drop All Tables MySQL Drop MySQL Error Code 1175 MySQL Events MySQL Except MYSQL Foreign Key Constraint MySQL If Exists MySQL IndexOf MySQL List All Tables json_extract in MySQL TIMESTAMPDIFF in MySQL MySQL Syntax Checker Sudo MySQL Secure Installation

MySQL Join
2022-03-12 13:47:08

MySQL Join

The relational database system needs to interconnect multiple tables with each other. MySQL is a popular and easy data management system to connect multiple tables. The foreign key is used to connect one table with another table using a similar column. MySQL Joins remove the drawback of a foreign key.

Join in MySQL helps to connect either a single table or multiple tables using matching columns. There are several types to joins columns of the tables. The join types are depended on the table columns and their position. Join operation helps to improve table data and its format. The join works on the command-line client interface and workbench interface both. The command-line interface uses a query to perform join operation in MySQL.

Types of MySQL JOIN

The basic MySQL join operations are inner join, left join, and right join. It also support the cross joins to works with complex operation. The following table illustrates the MySQL join types and their descriptions:

MySQL Joins Description
Inner join MySQL inner join returns the record by connecting common columns of the two tables. It is the basic join of the MySQL system.
Left join MySQL left join interconnects the two tables with each other. The right table column connects with the complete left side table.
Right join MySQL right join interconnects the two tables with each other. The left table column connects with the complete right side table.
Cross join The cross join connects any column of the multiple tables. Thus, this join interconnects multiple columns in multiple tables.
Self Join The table column interconnects with itself. Thus, the single table connects its column.

Operations of the MySQL join

The following table shows join operation and its description. You can create, update, and delete joins using the "join" keyword.

Join Operation Description
Create join Create required join type as per system requirement.
Update join Modify and operate join in the single or multiple tables.
Delete Join Delete the unwanted joins in the table.
Natural join The natural join combines multiple tables using a column with the same name and data type.

Uses of the MySQL join

  • MySQL join retrieves data from multiple tables.
  • The join removes the drawback of the foreign key connection.
  • It helps to operate large-scale data of the application.
  • It can join the single or multiple tables.

Syntax of the MySQL types

  • The join syntax are given below.
MySQL expression Table1 JOIN table2 condition;
  • The inner join syntax are given below.
MySQL expression Table1 INNER JOIN table2 condition;
  • The left join syntax are given below.
MySQL expression Table1 LEFT [OUTER] JOIN table2 condition;
  • The right join syntax are given below.
MySQL expression Table1 RIGHT [OUTER] JOIN table2 condition;

Prerequisite for the MySQL join

  • Select the required database for the MySQL table.
  • Create the first table with the required columns.
CREATE TABLE name1 (
Column1 data type constraint,
Column2 data type constraint);
  • Insert data into the first table.
Insert into table name (column list) values (values);
  • Create a second table with the required columns.
CREATE TABLE name1 (
Column1 data type constraint,
Column2 data type constraint);
  • Insert data into the second table.
Insert into table name (column list) values (values);

Example

Let us understand the join operation with the below example.

  • Create first table named department with the required columns.
mysql> CREATE TABLE department (
    	department_id INT AUTO_INCREMENT,
    	department_name VARCHAR(45),
    	admissions INT,
    	PRIMARY KEY (department_id));
  • Insert data into the department table. Here, the table includes the department name and number of the admissions.
mysql> INSERT INTO department (department_name, admissions) VALUES("Bachelor of Arts", 45), ("Bachelor of Commerce", 55), ("Bachelor of Engineering", 50), ("Bachelor of Technology", 60);
  • Create a second table named subject with the required columns.
mysql> CREATE TABLE subject (
subject_id INT AUTO_INCREMENT,
subject_name VARCHAR(45),
students INT,
PRIMARY KEY (subject_id));
  • Insert data into the subject table. Here, the table includes the subject name and number of the students.
mysql> INSERT INTO subject (subject_name, students) VALUES("Electronics", 20), ("Electrical", 25), ("Computer Science", 15), ("Information Technology", 30);
  • You can see the output of the department table structure and its data using the below query.
mysql> SELECT * FROM department;
MySQL JOIN

In the above image, you can see that the table contains department id, department name, and the number of the admissions.

  • You can see the output of the subject table structure and its data using the below query.
mysql> SELECT * FROM subject;
MySQL JOIN

In the above image, you can see that the table contains the subject id, subject name, and the number of the student.

Difference between inner join, natural join, and Equi join

The following table shows the main differences between the multiple joins.

Inner join Natural join Equi join
The inner join connects several tables using columns based on the "ON" clause. The natural join connects several tables using the same column names and their data type. The equi join connects the table using similar or equal columns and the associate table.
This join retrieves the column using the match column as per the ON condition. This join retrieves unique columns of the various tables. This join retrieves column with is equal or match as per join condition.
The inner join syntax shows below. SELECT
Column1 data type constraint,
Column2 data type constraint,
FROM table name1
INNER JOIN table2 ON condition1; 		The natural join syntax shows below.
SELECT *FROM table name1
NATURAL JOIN table2 ON condition1; 		The equi join syntax shows below.
SELECT * FROM table1 JOIN table2 ON table1.Column = table2.Column; 

Comparison between union and join

The following table shows similarities and differences between joins and union.

Joins Union
The joins use to retrieve table records from several tables. The union uses for combines the output data of the multiple tables.
The union works on data using a new column. The union works on data using new columns.
The syntax of the MySQL union shows below. The syntax of the MySQL union below.
The join has five types. It uses inner join, left join, right join, cross join, self join. The union has two types. It commonly uses the "union" and "union all" types.