I have two tables inside of the database Employees:

• Table employee

  • emp_number PRIMARY KEY (INT)
  • first_name (TEXT)
  • last_name (TEXT)

• Table salary

  • emp_number FOREIGN KEY employee.emp_number
  • salary (INT)

I would like to fetch all employees that make a salary over 80,000.

CodePudding user response：

What you are looking for is a JOIN operation which can be used to fetch a result of results based on values from one or more tables. By combining tables into one result, you can then filter the ones that match by foreign key using the ON statement.

SELECT e.first_name, e.last_name
FROM employees e
INNER JOIN salary s
ON s.emp_number = e.emp_number
WHERE salary.salary > 80000

CodePudding user response：

Here you can simply use Inner JOIN to join the two tables. and sometimes you might wonder what is the em and s. It's called SQL aliases. which are used to give a table, or a column in a table, a temporary name.

SELECT em.first_name,
       e.last_name
FROM   employees em
       INNER JOIN salary s
               ON s.emp_number = em.emp_number
WHERE  s.salary > 80000 

Read more about SQL JOIN , WHERE Clause and aliases from here

SQL Aliases

MySQL greater than or equal operator

SQL Joins