”;
A Join clause in MySQL is used to combine records from two or more tables in a database. These tables are joined together based on a condition, specified in a WHERE clause.
For example, comparing the equality (=) of values in similar columns of two different tables can be considered as a join-predicate. In addition, several operators can be used to join tables, such as <, >, <>, <=, >=, !=, BETWEEN, LIKE, and NOT etc.
We can use JOINS in the SELECT, UPDATE and DELETE statements to join the MySQL tables.
Types of Joins
There are various types of Joins provided by SQL which are categorized based on the way data across multiple tables are joined together. They are listed as follows −
-
Inner Join − An Inner Join retrieves the intersection of two tables. It compares each row of the first table with each row of the second table. If the pairs of these rows satisfy the join-predicate, they are joined together. This is a default join.
-
Outer Join − An Outer Join retrieves all the records in two tables even if there is no counterpart row of one table in another table, like Inner Join. Outer join is further divided into three subtypes: Left Join, Right Join and Full Join. We will learn about these Joins later in this tutorial.
Example
In this example, we first create a table named CUSTOMERS using the CREATE TABLE query as follows −
CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR(15) NOT NULL, AGE INT NOT NULL, ADDRESS VARCHAR(25), SALARY DECIMAL(10, 2), PRIMARY KEY(ID) );
Let us then insert the following records in the CUSTOMERS table −
INSERT INTO CUSTOMERS VALUES (1, ''Ramesh'', ''32'', ''Ahmedabad'', 2000), (2, ''Khilan'', ''25'', ''Delhi'', 1500), (3, ''Kaushik'', ''23'', ''Kota'', 2500), (4, ''Chaitali'', ''26'', ''Mumbai'', 6500), (5, ''Hardik'',''27'', ''Bhopal'', 8500), (6, ''Komal'', ''22'', ''Hyderabad'', 9000), (7, ''Muffy'', ''24'', ''Indore'', 5500);
The table is created as −
| ID | NAME | AGE | ADDRESS | SALARY |
|---|---|---|---|---|
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | Kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | Hyderabad | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
ORDERS Table −
We create another table named ORDERS containing details of orders made by the customers, using the following CREATE TABLE query −
CREATE TABLE ORDERS ( OID INT NOT NULL, DATE VARCHAR (20) NOT NULL, CUSTOMER_ID INT NOT NULL, AMOUNT DECIMAL (18, 2) );
Using the INSERT statement, insert values into the ORDERS table as follows −
INSERT INTO ORDERS VALUES (102, ''2009-10-08 00:00:00'', 3, 3000.00), (100, ''2009-10-08 00:00:00'', 3, 1500.00), (101, ''2009-11-20 00:00:00'', 2, 1560.00), (103, ''2008-05-20 00:00:00'', 4, 2060.00);
The table is displayed as follows −
| OID | DATE | CUSTOMER_ID | AMOUNT |
|---|---|---|---|
| 102 | 2009-10-08 00:00:00 | 3 | 3000.00 |
| 100 | 2009-10-08 00:00:00 | 3 | 1500.00 |
| 101 | 2009-11-20 00:00:00 | 2 | 1560.00 |
| 103 | 2008-05-20 00:00:00 | 4 | 2060.00 |
Joining Tables −
Now we write an SQL query to join these two tables. This query will select all the customers from table CUSTOMERS and will pick up the corresponding number of orders made by them from the ORDERS.
SELECT a.ID, a.NAME, b.DATE, b.AMOUNT FROM CUSTOMERS a, ORDERS b WHERE a.ID = b.CUSTOMER_ID;
Output
The table is displayed as follows −
| ID | NAME | DATE | AMOUNT |
|---|---|---|---|
| 3 | Kaushik | 2009-10-08 00:00:00 | 3000.00 |
| 3 | Kaushik | 2009-10-08 00:00:00 | 1500.00 |
| 2 | Khilan | 2009-11-20 00:00:00 | 1560.00 |
| 4 | Chaitali | 2008-05-20 00:00:00 | 2060.00 |
Joins Using a Client Program
In addition to Join two or more than two tables using the MySQL query we can also perform the Join operation using a client program.
Syntax
To join two or more than two MySQL tables through a PHP program, we need to perform the JOIN operation using the mysqli function query() as follows −
$sql = ''SELECT a.ID, a.NAME, b.DATE, b.AMOUNT FROM CUSTOMERS a, ORDERS b WHERE a.ID = b.CUSTOMER_ID''; $mysqli->query($sql);
To join two or more than two MySQL tables through a JavaScript program, we need to perform the JOIN operation using the query() function of mysql2 library as follows −
sql = ''SELECT a.ID, a.NAME, b.DATE, b.AMOUNT FROM CUSTOMERS a, ORDERS b WHERE a.ID = b.CUSTOMER_ID''; con.query(sql);
To join two or more than two MySQL tables through a Java program, we need to perform the JOIN operation using the JDBC function executeQuery() as follows −
String sql = ''SELECT a.ID, a.NAME, b.DATE, b.AMOUNT FROM CUSTOMERS a, ORDERS b WHERE a.ID = b.CUSTOMER_ID''; st.executeQuery(sql);
To join two or more than two MySQL tables through a Python program, we need to perform the JOIN operation using the execute() function of the MySQL Connector/Python as follows −
using_join_query = ''SELECT a.ID, a.NAME, b.DATE, b.AMOUNT FROM CUSTOMERS a, ORDERS b WHERE a.ID = b.CUSTOMER_ID'' cursorObj.execute(using_join_query)
Example
Following are the programs −
$dbhost = ''localhost'';
$dbuser = ''root'';
$dbpass = ''password'';
$dbname = ''TUTORIALS'';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($mysqli->connect_errno) {
printf("Connect failed: %s
", $mysqli->connect_error);
exit();
}
printf(''Connected successfully.
'');
$sql = ''SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a JOIN tcount_tbl b
ON a.tutorial_author = b.tutorial_author'';
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
printf(
"Id: %s, Author: %s, Count: %d
",
$row["tutorial_id"],
$row["tutorial_author"],
$row["tutorial_count"]
);
}
} else {
printf(''No record found.
'');
}
mysqli_free_result($result);
$mysqli->close();
Output
The output obtained is as follows −
Id: 3, Author: Sanjay, Count: 1
var mysql = require("mysql2");
var con = mysql.createConnection({
host: "localhost",
user: "root",
password: "password",
}); //Connecting to MySQL
con.connect(function (err) {
if (err) throw err;
// console.log("Connected successfully...!");
// console.log("--------------------------");
sql = "USE TUTORIALS";
con.query(sql);
sql = "SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a JOIN tcount_tbl b ON a.tutorial_author = b.tutorial_author";
con.query(sql, function (err, result) {
if (err) throw err;
console.log(result);
});
});
Output
The output produced is as follows −
[ { tutorial_id: 1, tutorial_author: ''John Poul'', tutorial_count: 2 } ]
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Join {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/TUTORIALS";
String username = "root";
String password = "password";
try {
Class.forName("com.mysql.cj.jdbc.Driver");
Connection connection = DriverManager.getConnection(url, username, password);
Statement statement = connection.createStatement();
System.out.println("Connected successfully...!");
//Mysql JOIN...!;
String sql = "SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a JOIN tcount_tbl b ON a.tutorial_author = b.tutorial_author";
ResultSet resultSet = statement.executeQuery(sql);
System.out.println("Table records after join...!");
while (resultSet.next()){
System.out.println(resultSet.getString(1)+ " "+ resultSet.getString(2)+" "+resultSet.getString(3));
}
connection.close();
} catch (Exception e) {
System.out.println(e);
}
}
}
Output
The output obtained is as shown below −
Connected successfully...! Table records after join...! 1 John Paul 1 3 Sanjay 1
import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
host=''localhost'',
user=''root'',
password=''password'',
database=''tut''
)
cursorObj = connection.cursor()
using_join_query = f"""SELECT ID, NAME, AGE, AMOUNT FROM CUSTOMERS JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUST_ID"""
cursorObj.execute(using_join_query)
# Fetching all the rows that meet the criteria
filtered_rows = cursorObj.fetchall()
for row in filtered_rows:
print(row)
cursorObj.close()
connection.close()
Output
Following is the output of the above code −
(3, ''Kaushik'', 23, 3000) (3, ''Kaushik'', 23, 1500) (2, ''Khilan'', 25, 1560) (4, ''Chaital'', 25, 2060)
”;