Summary -

In this topic, we described about the Select Multiple with detailed example.

There are several ways to select multiple rows and multiple columns from existing table. The possible syntaxes that to fetch multiple columns and rows from existing table are listed as below -

Syntax -

SELECT column1,column2, …, columnN
FROM table_name;   
  • column1,column2 – Specifies the name of the columns used to fetch.
  • table_name - Specifies the name of the table.

Example -

Let us consider below table(s) as an example table(s) to frame the SQL query for getting the desired results.

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Scenario – Fetch all rows.

Requirement – Fetch all employees from employee_details table. The query was as follows -

SELECT * FROM employee_details; 

By executing above query, we can get results as shown below –

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Scenario – Fetch specific columns with all row's information.

Requirement - Fetch employee details (emp_id, emp_name, designation) from employee_details table. The query was as follows -

SELECT emp_id, emp_name, designation FROM employee_details; 

By executing above query, we can get results as shown below –

emp_id emp_name designation
001 Employee1 Director
002 Employee2 Director
003 Employee3 Manager
004 Employee4 Manager
005 Employee5 Analyst
006 Employee6 Analyst
007 Employee7 Clerk
008 Employee8 Salesman
009 Employee9 Salesman

Scenario – Fetch all rows that satisfies a condition.

Requirement - Fetch all employees those belongs to department 1000 from employee_details table. The query was as follows -

 SELECT * FROM employee_details WHERE dept_id = 1000; 

By executing above query, we can get results as shown below –

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000

Scenario – Fetch unique designations using DISTINCT clause.

Requirement – Fetch unique designation from employee_details table. The query was as follows –

SELECT DISTINCT designation FROM employee_details; 

By executing above query, we can get results as shown below –

designation
Analyst
Clerk
Director
Manager
Salesman

Scenario – Fetch specific column that satisfies the condition with AND operator.

Requirement – Fetch employee details(emp_id, emp_name, designation, dept_id) that belongs to department 1000 from employee_details table. The query was as follows -

SELECT emp_id, emp_name, designation, dept_id
FROM employee_details
WHERE dept_id = "1000" AND designation = "Analyst"; 

By executing above query, we can get results as shown below –

emp_id emp_name designation dept_id
001 Employee1 Director 1000
003 Employee3 Manager 1000
005 Employee5 Analyst 1000
006 Employee6 Analyst 1000
007 Employee7 Clerk 1000

Scenario – Fetch all rows using IN operator.

Requirement – Fetch employees details those emp_id in 003, 004, 005 from employee_details table. The query was as follows -

SELECT * FROM employee_details WHERE emp_id IN (003, 004, 005); 

By executing above query, we can get results as shown below -

emp_id emp_name designation manager_id date_of_hire salary dept_id
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000