SQL Left Outer Join Tutorial with Example: Employee Bonus
- 时间:2020-10-05 13:15:44
- 分类:网络文摘
- 阅读:81 次
SQL Left Outer Join can be illustrated by the following:

sql-left-outer-join
where the query will take everything from left table and those fields from the right table. Let’s take a look at the following question as a left-outer-join (which is known as left-join) example.
Employee Bonus
Select all employee’s name and bonuses whose bonus is less than $1000.
SQL Schema
Create table If Not Exists Employee (EmpId int, Name varchar(255), Supervisor int, Salary int) Create table If Not Exists Bonus (EmpId int, Bonus int) Truncate table Employee insert into Employee (EmpId, Name, Supervisor, Salary) values ('3', 'Brad', 'None', '4000') insert into Employee (EmpId, Name, Supervisor, Salary) values ('1', 'John', '3', '1000') insert into Employee (EmpId, Name, Supervisor, Salary) values ('2', 'Dan', '3', '2000') insert into Employee (EmpId, Name, Supervisor, Salary) values ('4', 'Thomas', '3', '4000') Truncate table Bonus insert into Bonus (EmpId, Bonus) values ('2', '500') insert into Bonus (EmpId, Bonus) values ('4', '2000')
Table:Employee
+-------+--------+-----------+--------+ | empId | name | supervisor| salary | +-------+--------+-----------+--------+ | 1 | John | 3 | 1000 | | 2 | Dan | 3 | 2000 | | 3 | Brad | null | 4000 | | 4 | Thomas | 3 | 4000 | +-------+--------+-----------+--------+empId is the primary key column for this table.
Table: Bonus
+-------+-------+ | empId | bonus | +-------+-------+ | 2 | 500 | | 4 | 2000 | +-------+-------+empId is the primary key column for this table.
Example ouput:
+-------+-------+ | name | bonus | +-------+-------+ | John | null | | Dan | 500 | | Brad | null | +-------+-------+
The SQL of left-join will return all the employees and their bonuses, however, some employees don’t have bonuses – as they are not shown in the Bonus table. Therefore, the result field (bonus) will be empty for those employees. Thus, we have to explicitly use IS (or IS NOT) operator to check for nullness.
SELECT Employee.name, Bonus.bonus FROM Employee LEFT JOIN Bonus ON Employee.empid = Bonus.empid WHERE bonus < 1000 OR bonus IS NULL;
–EOF (The Ultimate Computing & Technology Blog) —
推荐阅读:IPv6 Is Dominant, But Has The World Actually Moved On? How to Find the Town Judge using Graph Algorithm? The Backspace String Compare Algorithm The Alien Dictionary Verification Algorithm How to Sum the Root To Leaf in Binary Numbers in a Binary Tree u Getting Known – How To Make Your Blog Into A Brand How To Charge Clients For Your Blogging Work The 5 Best Ways to Learn How to Improve Your Blog What You Need to Know About the Ghost Open Source Blogging Platf How to Build a Sustainable Business Out of Your Blog
- 评论列表
-
- 添加评论