untung99.homes: SQL Left Join
Untung99 menawarkan beragam permainan yang menarik, termasuk slot online, poker, roulette, blackjack, dan taruhan olahraga langsung. Dengan koleksi permainan yang lengkap dan terus diperbarui, pemain memiliki banyak pilihan untuk menjaga kegembiraan mereka. Selain itu, Untung99 juga menyediakan bonus dan promosi menarik yang meningkatkan peluang kemenangan dan memberikan nilai tambah kepada pemain.
Berikut adalah artikel atau berita tentang Harian untung99.homes dengan judul untung99.homes: SQL Left Join yang telah tayang di untung99.homes terimakasih telah menyimak. Bila ada masukan atau komplain mengenai artikel berikut silahkan hubungi email kami di koresponden@untung99.homes, Terimakasih.
Joins are used to retrieve records from two or more tables based on a logical relation between them. This relation is defined using a join condition.
When we join two tables based on a condition, inner join, provides the intersection values of these i.e. records from both tables that satisfy the join condition.
What is Outer Join?
Unlike inner join the outer join may contain the records that doesn’t satisfy the join condition along with the records that satisfy it. There are three types of outer join namely −
- Left Outer Join
- Right Outer Join
- Full Outer Join
Whenever we join to tables if the left table (or the first table) has more records than the right table (or the second table), or vice versa, outer join displays NULL values for the records that do not have their counterparts (where the join condition is not met).
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.
Left Join in SQL
Left Join or Left Outer Join in SQL combines two or more tables, where the first table is returned wholly; but, only the matching record(s) are retrieved from the consequent tables. If zero (0) records are matched in the consequent tables, the join will still return a row in the result, but with NULL in each column from the right table.
Note − If the number of rows in first table is less than the number of rows in second table, the rows 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 SQL −
SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2 ON table_name1.column_name = table_name2.column_name
Example
To understand this query better, let us create some tables in an existing database and try to join them using Left Join or Left Outer Join.
Assume we have created a table named Customers, which contains the personal details of customers including their name, age, address and salary, using the following query.
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 (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'kaushik', 23, 'Kota', 2000.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (6, 'Komal', 22, 'MP', 4500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (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 | MP | 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 (OID, DATE, CUSTOMER_ID, AMOUNT) VALUES (102, '2009-10-08 00:00:00', 3, 3000.00); INSERT INTO ORDERS (OID, DATE, CUSTOMER_ID, AMOUNT) VALUES (100, '2009-10-08 00:00:00', 3, 1500.00); INSERT INTO ORDERS (OID, DATE, CUSTOMER_ID, AMOUNT) VALUES (101, '2009-11-20 00:00:00', 2, 1560.00); INSERT INTO ORDERS (OID, DATE, CUSTOMER_ID, AMOUNT) VALUES (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 | +-----+---------------------+-------------+---------+
Using the left join query below, we are trying to retrieve the details of customers who made an order at the specified date and who did not. 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 resultant table is obtained as −
+----+----------+---------------------+---------+ | ID | NAME | DATE | AMOUNT | +----+----------+---------------------+---------+ | 1 | Ramesh | NULL | NULL | | 2 | Khilan | 2009-11-20 00:00:00 | 1560.00 | | 3 | Kaushik | 2009-10-08 00:00:00 | 1500.00 | | 3 | Kaushik | 2009-10-08 00:00:00 | 3000.00 | | 4 | Chaitali | 2008-05-20 00:00:00 | 2060.00 | | 5 | Hardik | NULL | NULL | | 6 | Komal | NULL | NULL | | 7 | Muffy | NULL | NULL | +----+----------+---------------------+---------+
As we can see in the table above, only Khilan, Kaushik and Chaitali made purchases on the mentioned dates in Orders table; hence, the records are matched. The other customers in Customer table did not make purchases on the specified dates, so the records are returned as NULL.
Joining Multiple Tables with Left Join
Similar to the Inner Join query, 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.
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 try to create the Employee table 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); INSERT INTO EMPLOYEE VALUES (100, 'ALEKHYA', 3623); INSERT INTO EMPLOYEE VALUES (101, 'REVATHI', 1291); INSERT INTO EMPLOYEE VALUES (103, 'VIVEK', 3426);
The Employee table consists of the details of employees in an organization and sales made by them.
+-----+---------------+------------+ | EID | EMPLOYEE_NAME | SALES_MADE | +-----+---------------+------------+ | 102 | SARIKA | 4500 | | 100 | ALEKHYA | 3623 | | 101 | REVATHI | 1291 | | 103 | VIVEK | 3426 | | 100 | ALEKHYA | 3456 | +-----+---------------+------------+
Left Join Query
Let us try to 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;
Through this query, we are trying to display the records of Customer IDs, Customer names, Orders made on specific dates and names of the employees that sold them.
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 | 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 | +----+----------+---------------------+---------------+
As we can see in the table above, the customer Kaushik made three orders, in which two are sold by employee Alekhya and one is sold by Sarika. Khilan and Chaitali made one order each, that are sold by Revathi and Vivek respectively. The dates on which these orders are made will also be displayed. If the orders are not made on the specific dates, NULL is returned.
Left Join with WHERE Clause
Along with the ON clause, a WHERE clause can also be applied on the resultant table obtained after Left Join is implemented. Doing this will filter the data further.
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 try to 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 resultant table after applying the where clause with left join contains the rows that has amount values greater than 2000.00 −
+----+----------+---------------------+---------+ | ID | NAME | DATE | AMOUNT | +----+----------+---------------------+---------+ | 3 | Kaushik | 2009-10-08 00:00:00 | 3000.00 | | 4 | Chaitali | 2008-05-20 00:00:00 | 2060.00 | +----+----------+---------------------+---------+