Joins in SQL

Query to select first_name, incentive amount from employee and incentives table for those employees who have incentives.

select FIRST_NAME, INCENTIVE_AMOUNT from employee A inner join incentives B on A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID

Query to select first_name, incentive amount from employee and incentives table for those employees who have incentives and incentive amount greater than 3000.

select FIRST_NAME, INCENTIVE_AMOUNT from employee A inner join incentives B on A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID and INCENTIVE_AMOUNT > 3000

Query to select first_name, incentive amount from employee and incentives table for all employees even if they didn’t get incentives.

select FIRST_NAME, INCENTIVE_AMOUNT from employee A left join incentives B on A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID

Query to select first_name, incentive amount from employee and incentives table for all employees even if they didn’t get incentives and set incentive amount as 0 for those employees who didn’t get incentives.

select FIRST_NAME, IFNULL(INCENTIVE_AMOUNT,0) from employee A left join incentives B on A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID

Query to select first_name, incentive amount from employee and incentives table for all employees who got incentives using right join.

select FIRST_NAME, IFNULL(INCENTIVE_AMOUNT,0) from employee A right join incentives B on A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID

Query to select max incentive with respect to employee from employee and incentives table using sub query.

select DEPARTMENT,(select IFNULL(max(INCENTIVE_AMOUNT),0) from INCENTIVES where EMPLOYEE_REF_ID=EMPLOYEE_ID) Max_incentive from EMPLOYEE