MySQL Tutorial

This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

Left Join
Unlike inner join, which provides the intersection values of two tables, there is another type of join called Outer Join. This outer join provides the collection of matched and unmatched records of two tables in multiple cases.
MySQL Left Join
Left Join is a type of outer join that retrieves all the records from the first table and matches them to the records in second table.
If the records in left table do not have their counterparts in the second table, NULL values are added.
But, if the number of records in first table is less than the number of records in second table, the records in second table that do not have any counterparts in the first table will be discarded from the result.
﻿
Syntax
Following is the basic syntax of Left Join in MySQL −
SELECT table1.column1, table2.column2...
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;
﻿
Example
Using the following query, let us create a table named CUSTOMERS, that contains the personal details of customers including their name, age, address and salary.
CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);
﻿
Now insert values into this table using the INSERT statement as follows −
INSERT INTO CUSTOMERS VALUES 
(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);
﻿
The table will be 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
Let us create another table ORDERS, containing the details of orders made and the date they are made on.
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 this 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
Left Join Query:
Using the following left join query, we will retrieve the details of customers who made an order at the specified date. If there is no match found, the query below will return NULL in that record.
SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
LEFT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;
﻿
Output
The joined result-set is obtained as −
ID
NAME
AMOUNT
DATE
1
Ramesh
NULL
NULL
2
Khilan
1560.00
2009-11-20 00:00:00
3
Kaushik
1500.00
2009-10-08 00:00:00
3
Kaushik
3000.00
2009-10-08 00:00:00
4
Chaitali
2060.00
2008-05-20 00:00:00
5
Hardik
NULL
NULL
6
Komal
NULL
NULL
7
Muffy
NULL
NULL
Joining Multiple Tables with Left Join
Left Join also joins multiple tables where the first table is returned as a whole and the next tables are matched with the rows in the first table. If the records are not matched, NULL is returned.
Syntax
The syntax to join multiple tables using Left Join is given below −
SELECT column1, column2, column3...
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name
LEFT JOIN table3
ON table2.column_name = table3.column_name
.
.
.
﻿
Example
To demonstrate Left Join with multiple tables, let us consider the previously created tables CUSTOMERS and ORDERS. In addition to these we will create another table named EMPLOYEE, which consists of the details of employees in an organization and sales made by them, using the following query −
CREATE TABLE EMPLOYEE (
   EID INT NOT NULL,
   EMPLOYEE_NAME VARCHAR (30) NOT NULL,
   SALES_MADE DECIMAL (20)
);
﻿
Now, we can insert values into this empty tables using the INSERT statement as follows −
INSERT INTO EMPLOYEE VALUES
(102, 'SARIKA', 4500),
(100, 'ALEKHYA', 3623),
(101, 'REVATHI', 1291),
(103, 'VIVEK', 3426);
﻿
The table is created as −
EID
EMPLOYEE_NAME
SALES_MADE
102
SARIKA
4500
100
ALEKHYA
3623
101
REVATHI
1291
103
VIVEK
3426
Left Join Query:
Let us join these three tables using the left join query given below −
SELECT CUSTOMERS.ID, CUSTOMERS.NAME, ORDERS.DATE, EMPLOYEE.EMPLOYEE_NAME
FROM CUSTOMERS
LEFT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
LEFT JOIN EMPLOYEE
ON ORDERS.OID = EMPLOYEE.EID;
﻿
Output
The resultant table is obtained as follows −
ID
NAME
DATE
EMPLOYEE_NAME
1
Ramesh
NULL
NULL
2
Khilan
2009-11-20 00:00:00
REVATHI
3
Kaushik
2009-10-08 00:00:00
ALEKHYA
3
Kaushik
2009-10-08 00:00:00
SARIKA
4
Chaitali
2008-05-20 00:00:00
VIVEK
5
Hardik
NULL
NULL
6
Komal
NULL
NULL
7
Muffy
NULL
NULL
Left Join with WHERE Clause
To filter the records after joining two tables, a WHERE clause can be applied.
Syntax
The syntax of Left Join when used with WHERE clause is given below −
SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name = table_name2.column_name
WHERE condition
﻿
Example
Records in the combined database tables can be filtered using the WHERE clause. Consider the previous two tables CUSTOMERS and ORDERS; and join them using the left join query by applying some constraints using the WHERE clause.
SELECT ID, NAME, DATE, AMOUNT FROM CUSTOMERS
LEFT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
WHERE ORDERS.AMOUNT > 2000.00;
﻿
Output
The output is obtained as − −
﻿
ID
NAME
DATE
AMOUNT
3
Kaushik
2009-10-08 00:00:00
3000.00
4
Chaitali
2008-05-20 00:00:00
2060.00
Left Join Using a Client Program
We can also perform the left join operation on one or more tables using a client program.
Syntax
PHPNodeJSJavaPython
To join two tables using left join through a PHP program, we need to execute the SQL query with LEFT JOIN clause using the mysqli function query() as follows −
$sql = 'SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a LEFT JOIN tcount_tbl b
ON a.tutorial_author = b.tutorial_author';
$mysqli->query($sql);
﻿
Example
Following are the programs −
PHPNodeJSJavaPython
$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 LEFT JOIN tcount_tbl b
			ON a.tutorial_author = b.tutorial_author';
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
    echo " following is the both table details after executing left join! \n";
    while ($row = $result->fetch_assoc()) {
        printf( "Id: %s, Author: %s, Count: %d", $row["tutorial_id"], $row["tutorial_author"], $row["tutorial_count"] );
        printf("\n");
    }
} else {
    printf('No record found.');
}
mysqli_free_result($result);
$mysqli->close();
﻿
Output
The output obtained is as follows −
following is the both table details after executing left join!
Id: 1, Author: John Poul, Count: 0
Id: 2, Author: Abdul S, Count: 0
Id: 3, Author: Sanjay, Count: 1
Id: 101, Author: Aman kumar, Count: 0
Id: 102, Author: Sarika Singh, Count: 0    
﻿
﻿
﻿
﻿
﻿
﻿
﻿

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

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

ID	NAME	AMOUNT	DATE
1	Ramesh	NULL	NULL
2	Khilan	1560.00	2009-11-20 00:00:00
3	Kaushik	1500.00	2009-10-08 00:00:00
3	Kaushik	3000.00	2009-10-08 00:00:00
4	Chaitali	2060.00	2008-05-20 00:00:00
5	Hardik	NULL	NULL
6	Komal	NULL	NULL
7	Muffy	NULL	NULL

MySQL Basics

MySQL Databases

MySQL Users

MySQL Tables

MySQL Queries

MySQL Views

MySQL Indexes

MySQL Operators and Clauses

MySQL Joins

MySQL Keys

MySQL Triggers

MySQL Data Types

MySQL Regular Expressions

MySQL Fulltext Search

MySQL Functions & Operators

MySQL Misc Concepts

MySQL Useful Resources

MySQL Tutorial

Left Join

MySQL Left Join

Syntax

Example

Output

Joining Multiple Tables with Left Join

Syntax

Example

Output

Left Join with WHERE Clause

Syntax

Example

Output

Left Join Using a Client Program

Syntax

Example

Output