SQL 合并两个表的数据

SQL 合并两个表的数据

1.通过共同的field value

display the first name and last name of employees working for the department which allotment amount is more than 50000.
self join?

SELECT emp_fname,emp_lname
FROM emp_details e1,emp_department e2             #combine two tables together
WHERE e1.emp_dept=e2.dpt_code
AND dpt_allotment >50000;;

https://www.w3resource.com/sql-exercises/subqueries/sql-subqueries-inventory-exercise-36.php

2.joins

SELECT emp_fname, emp_lname 
FROM emp_details as e1
INNER JOIN emp_department as e2
ON e1.emp_dept=e2.dpt_code
WHERE dpt_allotment > 50000;

3.subquery

SELECT emp_fname, emp_lname 
FROM emp_details
WHERE emp_dept  IN
        (SELECT dpt_code 
         FROM emp_department 
        WHERE dpt_allotment > 50000);

4.UNION,UNION ALL

combine column data
similar skema