MariaDB Left Join – Helpful Guide

In this MariaDB tutorial, we will study the use of the MariaDB Left Join clause and we will also cover some examples. There are lists of the topic that comes under discussion:

• MariaDB left join
• MariaDB left join example
• MariaDB left join not working
• MariaDB left join multiple tables
• MariaDB left join subquery
• MariaDB left join using
• MariaDB left join limit 1
• MariaDB left join order by
• MariaDB left join group by
• MariaDB left join count
• MariaDB left join max
• MariaDB left join where
• MariaDB Inner join Left join
• MariaDB Delete Left Join

MariaDB Left Join

In this section, we will learn how to use the MariaDB left Join clause to query data from multiple tables.

In MariaDB, the LEFT JOIN is used to return all rows from the left-hand of the table specified in the ON condition and only those rows from the other table where the condition is accepted.

The syntax of the MariaDB LEFT JOIN clause is given below:

SYNTAX:

SELECT columns  
FROM table_1  
LEFT [OUTER] JOIN table_2  
ON table_1.column = table_2.column;   

The syntax explanation:

• The LEFT JOIN clause starts selecting data from the left-hand table Table1 and it compares each row from table1 to table2.
• If both rows cause the join condition to be TRUE then the LEFT JOIN combines a column of rows from both tables into new rows and put it into a new resultset.
• In case, if they are not matched, the LEFT JOIN will still create a new row whose columns are the combination of both tables. It uses the NULL values for columns from the right table.

The LEFT JOIN clause diagram is given below:

In general, if we join tables by matching rows then we use the equal (=) operator and the use of the primary key of the left table (table_1) and foreign key of the right table (table_2).

First, let’s have a look at the USA_WESTONE and WALMART table by the following query:

SELECT * FROM USA_WESTONE;

SELECT * FROM WALMART;

In this query, we have retrieved all records from both tables USA_WESTONE and WALMART by using the SELECT statement.

The image of USA_WESTONE and WALMART table is given below by using the method of a SELECT statement:

EXAMPLE:

TABLE_1: USA_WESTONE (Left Hand Table)

TABLE_2: WALMART (Right-Hand Table)

The sample example of the MariaDB LEFT JOIN is given below:

EXAMPLE:

SELECT usa_westone.earphone_id, usa_westone.earphone_name,   
usa_westone.earphone_price,   
walmart.product_price, walmart.product_name  
FROM usa_westone  
LEFT JOIN walmart
ON usa_westone.earphone_id = walmart.product_id; 

In this query, the LEFT JOIN clause takes all records from the earphone_id, earphone_name and earphone_price column from the left-hand table as the USA_WESTONE table and the right-hand table as the WALMART table it took product_price and product_name column.

So based on the LEFT JOIN clause, it took all the records based on the TRUE condition which means total rows will come in the resultset based on the USA_WESTONE table records and put it in the query but not from the total records in the WALMART table.

Read: MariaDB Join with Examples

MariaDB Left Join Example

In this section, we will discuss one more example of the MariaDB LEFT JOIN clause.

The MariaDB LEFT JOIN is used to return all rows from the left-hand of the table specified in the ON condition and only those rows from the other table where the condition is accepted. The syntax of the MariaDB LEFT JOIN is given below:

First, let’s have a look at the USA_COSTCAWHOLESALE and USA_IKEA table by the following query below:

Query: USING SELECT statement

SELECT * FROM USA_COSTCAWHOLESALE;

SELECT * FROM USA_IKEA;

In MariaDB, the SELECT statement example will retrieve all records of both tables like USA_COSTCAWHOLESALE and USA_IKEA. The image of USA_COSTCAWHOLESALE and USA_IKEA table by using the SELECT statement is given below:

TABLE 1: USA_COSTCAWHOLESALE TABLE (left-hand table)

TABLE2: USA_IKEA (Right-hand table)

The sample example of MariaDB left Join for both table USA_COSTCAWHOLESALE and USA_IKEA table is given below:

EXAMPLE:

SELECT USA_COSTCAWHOLESALE.PRODUCT_ID,USA_COSTCAWHOLESALE.PRODUCT_NAME,USA_COSTCAWHOLESALE.PRODUCT_PRICE,
USA_IKEA.PRODUCT_NAME,USA_IKEA.PRODUCT_PRICE
FROM USA_COSTCAWHOLESALE  
LEFT JOIN USA_IKEA 
ON
USA_COSTCAWHOLESALE.PRODUCT_ID=USA_IKEA.PRODUCT_ID;

In this query, the left join example brings all records from the USA_COSTCAWHOLESALE table and some records from the USA_IKEA table based on the ON condition. If we see clearly that the USA_COSTCAWHOLESALE table had 20 records in it and the USA_IKEA had 16 records.

So while using the LEFT JOIN clause based on the condition accepted it provided the NULL values for both PRODUCT_PRICE and PRODUCT_PRICE column cause it took all records all records from the left-hand table as USA_COSTCAWHOLESALE table based on the LEFT JOIN clause procedure.

Read: MariaDB Truncate Table

MariaDB Left Join Not Working

In this section, we will understand if the MariaDB Left Join is not working with the help of examples.

The reason for the LEFT JOIN is not working is due to syntax error or logical error which is made by the user for the query in the resultset. Let me show you an example of left joins not working which is given below:

SELECT usa_westone.earphone_id, usa_westone.earphone_name,   
usa_westone.earphone_price,   
walmart.product_price, walmart.product_name  
FROM usa_westone  
LEFT JOIN walmart
usa_westone.earphone_id = walmart.product_id; 

In this query by mistake, we forgot to put the ON clause with the LEFT JOIN clause which is used for the common column as EARPHONE_ID from the USA_WESTONE table and PRODUCT_ID column from the WALMART table. and to give the new records in the resultset.

Now let’s try to use the same above query without any error for the new resultset which is given below:

SELECT usa_westone.earphone_id, usa_westone.earphone_name,   
usa_westone.earphone_price,   
walmart.product_price,walmart.product_name  
FROM usa_westone  
LEFT JOIN walmart
ON usa_westone.earphone_id =walmart.product_id; 

In this query, there is no error found and it provided the new resultset for the LEFT JOIN clause.

Read: MariaDB Set Variable

MariaDB Left Join Multiple Tables

In this section, we will join multiple tables with the help of the MariaDB LEFT JOIN clause with the given syntax and examples.

The LEFT JOIN is one of the types of join used in the MariaDB. MariaDB normally uses the JOIN clause to form a new table that takes out common data like records or tuples from both tables like TABLE_1 and TABLE_2 which have matching records in general.

So, when it comes to LEFT JOIN in MariaDB it would return all the records from the tuples or rows from the left-hand table and matching records from the right-hand table.

First, let’s have a look at the USA_HP and USA_COSTCAWHOLESALE table by the following query:

SELECT * FROM USA_HP;

SELECT * FROM USA_COSTCAWHOLESALE;

TABLE1: Using USA_HP table ( Left Hand Table)

TABLE2: Using USA_COSTCAWHOLESALE (Right-Hand Table)

To join two tables for the information and to get common information we can use the LEFT JOIN clause in the following query:

EXAMPLE:

SELECT usa_hp.`NAMES`,usa_hp.HP_DOLLARPRICE,usa_hp.HP_ORIGINALPRICE,usa_costcawholesale.Product_Name,
usa_costcawholesale.Product_Price 
FROM usa_hp
LEFT JOIN usa_costcawholesale
ON usa_hp.HP_ID=usa_costcawholesale.Product_Id;

In this query, the left join example brings all records from the USA_HP table and some records from the USA_COSTCAWHOLESALE table based on the ON condition. If we see clearly that the USA_HP table had 16 records in it and the USA_COSTCAWHOLESALE had 18 records.

So, while using the LEFT JOIN clause based on the condition accepted it provided rows or tuples from the HP_NAME, HP_DOLLARPRICE. And HP_ORIGINALPRICE column cause it took all records all the records from the left-hand table as USA_HP table based on the LEFT JOIN clause procedure.

Read: MariaDB Regexp + Examples

MariaDB Left Join Subquery

In this section, we will learn how to use the MariaDB Subquery clause with the LEFT JOIN clause with the given syntax and examples.

In MariaDB, a subquery is a query inside another query. We can create subqueries within SQL statements. These subqueries reside in the WHERE clause, in the FROM clause or also in the SELECT clause.

It is also known as INSIDE QUERY or INNER SELECT. The main subquery that contains subquery is known as OUTER QUERY or OUTER SELECT. A subquery can be used quite often but not in all cases which can be rewritten as a JOIN clause.

The syntax of the MariaDB left join subquery is given below:

SYNTAX:

SELECT TABLE_1.COLUMN_1,TABLE_1.COLUMN_2 
FROM TABLE_1,table_2 
WHERE TABLE_1.COMMON_COLUMN=TABLE_2.COMMON_COLUMN;

The syntax explanation:

• SELECT: The statement from which data to be retreived and there should be atleast one table listed in the FROM clause.
• FROM TABLE_1,TABLE_2: There should be subquery tables to form new table for the resultset.
• WHERE table_1.common_column= table_2.common_column: The condition where the result to be matched because of common column from both tables table_1 and table_2.

The sample example of the MariaDB Left Join subquery is given below:

EXAMPLE:

SELECT USA_IKEA.PRODUCT_NAME,USA_IKEA.PRODUCT_PRICE,USA_COSTCAWHOLESALE.PRODUCT_NAME
FROM USA_IKEA,USA_COSTCAWHOLESALE
WHERE USA_IKEA.PRODUCT_ID=USA_COSTCAWHOLESALE.PRODUCT_ID;

As we see in the above query, the SELECT statement creates a new result set and provides all records from the PRODUCT_NAME and PRODUCT_PRICE column from the left-hand table as the USA_IKEA table. And it gives less record from the product_name column of the right-hand table as USA_COSTCAWHOLESALE table.

It used the common column to give a new resultset from both the table USA_IKEA and USA_COSTCAWHOLESALE which was having the PRODUCT_ID as a common column.

Read: MariaDB Select Into + Examples

MariaDB Left Join Using

In this section, we will learn how to use the USING clause in the MariaDB LEFT JOIN clause by giving examples.

In MariaDB, the USING clause is used to names a list of columns that are listed in both tables. If the TABLE_1 and TABLE_2 both contains columns c1,c2 and c3. The syntax of using the USING clause with the MariaDB LEFT JOIN is given below:

SELECT TABLE_1.COLUMN1,TABLE_1.COLUMN2,TABLE_2.COLUMN1
FROM TABLE_1
LEFT JOIN TABLE_2 
ON TABLE_1.COMMON_COLUMN=TABLE_2.COMMON_COLUMN
USING (JOIN_COLUMN_LIST_1,JOIN_COLUMN_LIST_2);

The sample example of the USING clause with the MariaDB LEFT JOIN clause is given below:

SELECT USA_COSTCAWHOLESALE.PRODUCT_ID,USA_COSTCAWHOLESALE.PRODUCT_NAME,
USA_COSTCAWHOLESALE.PRODUCT_PRICE,
USA_IKEA.PRODUCT_NAME,USA_IKEA.PRODUCT_PRICE
FROM USA_COSTCAWHOLESALE  
LEFT JOIN USA_IKEA 
USING (PRODUCT_NAME,PRODUCT_PRICE);

In this query, the USING cause was used for PRODUCT_NAME and PRODUCT_PRICE column to be matched with rows of both tables USA_COSTCAWHOLESALE and USA_IKEA and give it as a resultset.

But due to the use of the LEFT JOIN clause, it carried all records from the USA_COSTCAWHOLESALE table because of the left-hand table and no records from the right-hand table as USA_IKEA table.

So, the USING clause didn’t provide any matching columns from the right-hand table as USA_IKEA in the resultset.

Read: MariaDB on Duplicate Key Update

MariaDB Left Join Limit 1

In the section, we will learn how to use the LIMIT clause with the MariaDB LEFT JOIN with the given syntax and examples.

In MariaDB, the SELECT statement is used with the LIMIT clause to retrieve one or more records from the table. The syntax of the LIMIT clause with the LEFT JOIN clause is given below:

SYNTAX:

SELECT columns  
FROM table1  
LEFT [OUTER] JOIN table2  
ON table1.column = table2.column
limit row_count;

The syntax explanation:

• columns: the column or expression that we want to retreive from both table by using the LEFT JOIN clause.
• from table1: The left hand table from which we will retreive records in the resultset. THere should be atleast one table with the FROM clause.
• left join table2: the connection with right hand table 2 with left hand table 1.
• ON table1.column=table2.column: the common column which conects both left hand and right hand table as primary key of one table to foreign key of another table.
• limit row_count: It specifies the limited rows in the resultset which will be returned from the row_count. For example, LIMIT 5 from the SELECT statement will return first 5 rows from the table.

The sample example of the LIMIT clause with the LEFT JOIN clause is given below:

EXAMPLE:

SELECT USA_COSTCAWHOLESALE.PRODUCT_ID,USA_COSTCAWHOLESALE.PRODUCT_NAME,USA_COSTCAWHOLESALE.PRODUCT_PRICE,
USA_IKEA.PRODUCT_NAME,USA_IKEA.PRODUCT_PRICE
FROM USA_COSTCAWHOLESALE  
LEFT JOIN USA_IKEA  
ON
USA_COSTCAWHOLESALE.PRODUCT_ID=USA_IKEA.PRODUCT_ID
LIMIT 1;

In this query, the left join example brings all records from the USA_COSTCAWHOLESALE table and some records from the USA_IKEA table based on the ON condition. If we see clearly that the USA_COSTCAWHOLESALE table had 20 records in it and the USA_IKEA had 16 records in it.

So while using the LEFT JOIN clause based on the condition accepted it provided the NULL values for both PRODUCT_PRICE and PRODUCT_PRICE column cause it took all records all records from the left-hand table as USA_COSTCAWHOLESALE table based on the LEFT JOIN clause procedure. And by using the LIMIT 1 in the LIMIT clause it provided the first row from both the table.

Read: MariaDB Window functions

MariaDB Left Join Order By

In this section, we will learn how to use the ORDER BY clause with the LEFT JOIN clause in the table and explained it with the help of syntax and examples.

The MariaDB Order By clause is used to sort the resultset in ascending or descending order. The ASC or DESC keyword is used after the ORDER BY clause. Normally the programmers don’t use the ASC keyword while arranging the result set in the ascending order cause it automatically arranges in that order without the ASC keyword.

The syntax of the ORDER BY clause with the LEFT JOIN clause is given below:

SYNTAX:

SELECT TABLE_1.SELECTED_COLUMN,TABLE_2.SELECTED_COLUMN 
FROM TABLE_1
LEFT JOIN TABLE_2 
ON 
TABLE_1.COMMON_COLUMN=TABLE_2.COMMON_COLUMN 
ORDER BY EXPR [ DESC | ASC];

The syntax explanation:

• TABLE_1.SELECTED_COLUMN,TABLE_2.SELECTED_COLUMN: The column that are selected and retrieved in the result set from the both tables i.e; TABLE_1 and TABLE_2.
• FROM: The tables from which you want to get records. At least one table must be mentioned in the FROM clause.
• LEFT JOIN TABLE_2: It is connected to another table like table_2.
• ON: The primary key column which is common in the both table used for connection.
• ORDER BY EXPR [DESC | ASC]: The expression or column that we want to arrange it in ascending or descending order by using ASC or DESC keyword.

The sample example of the ORDER BY clause with the LEFT JOIN clause is given below:

EXAMPLE:

SELECT USA_COSTCAWHOLESALE.PRODUCT_ID,USA_COSTCAWHOLESALE.PRODUCT_NAME,USA_COSTCAWHOLESALE.PRODUCT_PRICE,
USA_IKEA.PRODUCT_NAME,USA_IKEA.PRODUCT_PRICE
FROM USA_COSTCAWHOLESALE  
LEFT JOIN USA_IKEA  
ON
USA_COSTCAWHOLESALE.PRODUCT_ID=USA_IKEA.PRODUCT_ID
ORDER BY USA_COSTCAWHOLESALE.PRODUCT_NAME DESC;

In this query, the left join example brings all records from the USA_COSTCAWHOLESALE table and some records from the USA_IKEA table based on the ON condition.

And it also arranged the product_name column in descending order in the USA_COSTCAWHOLESALE table. There are some rows in the result set which will come NULL value because that product name might not be in another table.

If we see clearly that the USA_COSTCAWHOLESALE table had 20 records in it and the USA_IKEA had 16 records in it. So while using the LEFT JOIN clause based on the condition it provided the NULL values for both PRODUCT_PRICE and PRODUCT_PRICE columns.

This is because it took all records all records from the left-hand table as USA_COSTCAWHOLESALE table based on the LEFT JOIN clause procedure.

Read: MariaDB Comment – How to use

MariaDB Left Join Group By

In this section, we will learn how to use the GROUP BY clause with the LEFT JOIN clause in the table and explained it with the help of syntax and examples.

In a SELECT statement, the MariaDB GROUP BY clause is used to collect data from numerous records and group the results by one or more columns.

The syntax of the GROUP BY clause in the LEFT JOIN clause is given below:

SYNTAX:

SELECT TABLE_1.SELECTED_COLUMN,TABLE_2.SELECTED_COLUMN 
FROM TABLE_1
LEFT JOIN TABLE_2 
ON 
TABLE_1.COMMON_COLUMN=TABLE_2.COMMON_COLUMN 
GROUP BY [TABLE_1 | TABLE_2]. COLUMN;

The sample example of the GROUP BY clause in the LEFT JOIN clause is given below:

EXAMPLE:

SELECT USA_COSTCAWHOLESALE.PRODUCT_ID,USA_COSTCAWHOLESALE.PRODUCT_NAME,USA_COSTCAWHOLESALE.PRODUCT_PRICE,
USA_IKEA.PRODUCT_NAME,USA_IKEA.PRODUCT_PRICE
FROM USA_COSTCAWHOLESALE  
LEFT JOIN USA_IKEA  
ON
USA_COSTCAWHOLESALE.PRODUCT_ID=USA_IKEA.PRODUCT_ID
GROUP BY USA_COSTCAWHOLESALE.PRODUCT_PRICE;

As we see in the above query, the SELECT statement retrieves all records from the USA_COSTCAWHOLESALE table and some records from the USA_IKEA. It also did group the product_price column in the USA_COSTCAWHOLESALE table for the result set.

Read: MariaDB Row_Number Tutorial

MariaDB Left Join Count

In this section, we will learn how to use the COUNT() function in the LEFT JOIN clause in the MariaDB statement and it will be explained by syntax and examples.

The COUNT() function is used to return the count of the expressions. The syntax of the COUNT() function in the LEFT JOIN clause is given below:

SYNTAX:

SELECT COUNT([TABLE_1| TABLE_2].EXPRESSION), TABLE_1.COLUMN,
TABLE_2.COLUMN
FROM TABLE_1
LEFT JOIN TABLE_2
ON 
TABLE_1.COMMON_COLUMN = TABLE_2.COMMON_COLUMN;

The sample example of the COUNT() function in the LEFT JOIN clause is given below:

EXAMPLE:

SELECT USA_COSTCAWHOLESALE.PRODUCT_NAME,count(USA_COSTCAWHOLESALE.PRODUCT_PRICE),
USA_IKEA.PRODUCT_NAME,USA_IKEA.PRODUCT_PRICE
FROM USA_COSTCAWHOLESALE  
LEFT JOIN USA_IKEA  
ON
USA_COSTCAWHOLESALE.PRODUCT_ID=USA_IKEA.PRODUCT_ID;

In this query, the SELECT statement brings 20 counts in one row as the resultset based on the COUNT() function which was done on the PRODUCT_PRICE column in the USA_COSTCAWHOLESALE table.

Read: MariaDB Date Function with Examples

MariaDB Left Join Max

In this section, we will know about the MAX() function and use it with the LEFT JOIN clause by using the syntax and examples.

The MariaDB MAX() function is used to bring the maximum value in the result set. The sample example of the MAX() function with the LEFT JOIN clause is given below:

SELECT MAX( [TABLE_1 | TABLE_2].EXPRESSION),
TABLE_1.COLUMN,TABLE_2.COLUMN 
FROM TABLE_1
LEFT JOIN TABLE_2
ON 
TABLE_1.COMMON_COLUMN=TABLE_2.COMMON_COLUMN;

The sample of the MAX() function with the LEFT JOIN clause is given below:

EXAMPLE:

SELECT MAX(USA_COSTCAWHOLESALE.PRODUCT_NAME) AS MAX_VALUE,USA_COSTCAWHOLESALE.PRODUCT_PRICE,
USA_IKEA.PRODUCT_NAME,USA_IKEA.PRODUCT_PRICE
FROM USA_COSTCAWHOLESALE  
LEFT JOIN USA_IKEA  
ON
USA_COSTCAWHOLESALE.PRODUCT_ID=USA_IKEA.PRODUCT_ID;

• In this query, the SELECT statement brings the resultset of maximum value based on the product_name column in the USA_COSTCAWHOLESALE table.
• The max() function brings the MAX_VALUE column as ‘Voltaren Arthritis Pain Gel 12.34 Ounce Gel’ and product_price column as 899.99 from the USA_COSTCAWHOLESALE table.
• Based on the max function in the second table as USA_IKEA it brings product_name column as ‘Skubbs Bedsheets and Pillows‘ and product_price as 209.99.

Read: MariaDB Backup Database

MariaDB Left Join Where

In this section, we will learn how to use the WHERE condition with the LEFT JOIN clause in the MariaDB with the help of syntax and examples.

The MariaDB WHERE condition is used in the criteria that must be met for the recordings to be chosen. The syntax of the WHERE condition with the LEFT JOIN clause is given below:

SYNTAX:

SELECT TABLE_1.COLUMN,TABLE_2.COLUMN 
FROM TABLE_1
LEFT JOIN TABLE_2
ON 
TABLE_1.COMMON_COLUMN=TABLE_2.COMMON_COLUMN
WHERE [TABLE_1 |TABLE_2].CONDITION;

The sample example of the WHERE condition with the LEFT JOIN clause is given below:

EXAMPLE:

SELECT MAX(USA_COSTCAWHOLESALE.PRODUCT_NAME) AS MAX_VALUE,USA_COSTCAWHOLESALE.PRODUCT_PRICE,
USA_IKEA.PRODUCT_NAME,USA_IKEA.PRODUCT_PRICE
FROM USA_COSTCAWHOLESALE  
LEFT JOIN USA_IKEA  
ON
USA_COSTCAWHOLESALE.PRODUCT_ID=USA_IKEA.PRODUCT_ID
WHERE USA_COSTCAWHOLESALE.PRODUCT_PRICE>=100.99;

In this query, the SELECT statement is used with the WHERE condition in which we have to use the product_price column and to check its value is greater than or equal to 100.99.

As a result in the USA_COSTCAWHOLESALE table and the USA_IKEA table, it provided the result as 13 rows for both the product_name and product_price column.

Read: MariaDB Enable Remote Access

MariaDB Inner Join Left Join

In this section, we will learn about the INNER JOIN clause and LEFT JOIN clause with the syntax and examples.

In MariaDB, The INNER JOIN clause returns all rows from a multiple-table that meet the join constraints. The syntax of the INNER JOIN constraint is given below:

SYNTAX:

SELECT COLUMNS 
FROM TABLE_1
INNER JOIN TABLE_2
ON TABLE_1.COLUMN = TABLE_2.COLUMN;

The records where table_1 and table_2 intersect would be returned by the MariaDB INNER JOIN.

The sample example of the INNER JOIN clause is given below:

EXAMPLE:

SELECT USA_COSTCAWHOLESALE.PRODUCT_NAME,USA_COSTCAWHOLESALE.PRODUCT_PRICE,
USA_IKEA.PRODUCT_NAME,USA_IKEA.PRODUCT_PRICE
FROM USA_COSTCAWHOLESALE  
INNER JOIN USA_IKEA  
ON
USA_COSTCAWHOLESALE.PRODUCT_ID=USA_IKEA.PRODUCT_ID;

In this query, the INNER JOIN clause return rows from both the table i.e; USA_COSTCAWHOLESALE and USA_IKEA in which the condition is matched and by the common column PRODUCT_ID from both tables.

In the LEFT JOIN clause, it takes all records from the left-hand table_1 and some records from the right-hand table_2 and matches the query based on the common column from both the tables. The syntax of the LEFT JOIN clause is given below:

SYNTAX:

SELECT TABLE_1.COLUMN,TABLE_1.COLUMN_N,TABLE_2.COLUMN_1
TABLE_2.COLUMN_N FROM TABLE_1
LEFT JOIN TABLE_2
ON 
TABLE_1.COMMON_COLUMN=TABLE_2.COMMON_COLUMN;

The sample example of the LEFT JOIN clause with USA_COSTCAWHOLESALE and USA_IKEA table is given below:

EXAMPLE:

SELECT USA_COSTCAWHOLESALE.PRODUCT_NAME,USA_COSTCAWHOLESALE.PRODUCT_PRICE,
USA_IKEA.PRODUCT_NAME,USA_IKEA.PRODUCT_PRICE
FROM USA_COSTCAWHOLESALE  
LEFT JOIN USA_IKEA  
ON
USA_COSTCAWHOLESALE.PRODUCT_ID=USA_IKEA.PRODUCT_ID;

In this query, by using the LEFT JOIN clause it retrieves all records from the USA_COSTCAWHOLESALE table and some records from the USA_IKEA table. So, all the records came from both the tables with a common column which is PRODUCT_ID and it should be a primary key constraint while using any type in the JOIN clause.

Read: MariaDB ISNULL + Examples

MariaDB Delete Left Join

In this sub-topic, we will learn how to use the DELETE statement in the LEFT JOIN clause with the help of syntax and examples.

The LEFT JOIN clause in the SELECT statement is frequently used to locate rows in the left table that match or don’t match rows in the right table. It can also be used in the DELETE statement for deleting rows in the left table or don’t match rows in the right table.

The syntax of the LEFT JOIN clause with the DELETE statement is given below:

SYNTAX:

DELETE TABLE_1,TABLE_2
FROM TABLE_1
LEFT JOIN TABLE_2
ON
TABLE_1.COMMON_COLUMN-TABLE_2.COMMON_COLUMN
WHERE [CONDITIONS];

The syntax explanation:

• WHERE [conditions]: It is necessary to write the condition by using the WHERE clause otherwise no data willl be deleted from the both tables. And it is used to retreive selected records from the table.

The sample example of the LEFT JOIN clause with the DELETE statement is given below:

DELETE USA_HP,USA_westone
FROM USA_HP 
LEFT JOIN USA_westone  
ON
USA_HP.HP_ID=USA_WESTONE.EARPHONE_ID
WHERE USA_HP.HP_DOLLARPRICE>=100.99;

As we see in the above query, we have deleted all records fromUSA_HP (left-hand table) and some records from the USA_WESTONE table (right-hand table) based on the WHERE clause condition, the HP_DOLLARPRICE is greater than or equal to 100.99.

It shows that if the laptop price is greater than or equal to 100.99 then those records will be deleted from the USA_HP and some records from the USA_WESTONE table.

The output message is shown below:

/* Affected rows: 28  Found rows: 0  Warnings: 0  Duration for 1 query: 0.188 sec. */

Also, take a look at some more MariaDB tutorials.

• MariaDB Union Operator
• MariaDB Select Statement
• MariaDB Regexp + Examples
• MariaDB Temporary Table

In this MariaDB tutorial, we have studied the use of the MariaDB Left Join clause and we have also covered some examples. There are lists of the topic that comes under discussion:

• MariaDB left join
• MariaDB left join example
• MariaDB left join not working
• MariaDB left join multiple tables
• MariaDB left join subquery
• MariaDB left join using
• MariaDB left join limit 1
• MariaDB left join order by
• MariaDB left join group by
• MariaDB left join count
• MariaDB left join max
• MariaDB left join where
• MariaDB Inner join Left join
• MariaDB Delete Left Join