1 00:00:05,990 --> 00:00:08,400 Hello and welcome. 2 00:00:08,400 --> 00:00:10,875 In this video you'll learn how to write 3 00:00:10,875 --> 00:00:14,040 sub-queries or nested selects statements. 4 00:00:14,040 --> 00:00:18,420 Sub-queries or sub selects are like regular queries 5 00:00:18,420 --> 00:00:20,250 but placed within parentheses 6 00:00:20,250 --> 00:00:22,755 and nested inside another query. 7 00:00:22,755 --> 00:00:24,180 This allows you to form 8 00:00:24,180 --> 00:00:25,680 more powerful queries than 9 00:00:25,680 --> 00:00:27,825 would have been otherwise possible. 10 00:00:27,825 --> 00:00:31,080 An example of a nested query is shown. 11 00:00:31,080 --> 00:00:33,720 In this example, the sub-query is 12 00:00:33,720 --> 00:00:36,480 inside the where clause of another query. 13 00:00:36,480 --> 00:00:40,335 Consider the employees table from the previous video. 14 00:00:40,335 --> 00:00:43,520 The first few rows of data are shown here. 15 00:00:43,520 --> 00:00:45,830 The table contains several columns, 16 00:00:45,830 --> 00:00:47,720 including an employee ID, 17 00:00:47,720 --> 00:00:51,770 first name, last name, salary, etc. 18 00:00:51,770 --> 00:00:55,970 We will now go over some examples involving this table. 19 00:00:55,970 --> 00:00:58,190 Let's consider a scenario which may 20 00:00:58,190 --> 00:01:00,830 necessitate the use of sub-queries. 21 00:01:00,830 --> 00:01:03,650 Let's say, we want to retrieve the list of employees 22 00:01:03,650 --> 00:01:06,400 who earn more than the average salary. 23 00:01:06,400 --> 00:01:09,390 To do so, you could try this code. 24 00:01:09,390 --> 00:01:11,580 Select * from employees, 25 00:01:11,580 --> 00:01:14,320 where salary > AVG(salary). 26 00:01:14,750 --> 00:01:17,390 However, running this query will 27 00:01:17,390 --> 00:01:20,015 result in an error like the one shown. 28 00:01:20,015 --> 00:01:23,705 Indicating an invalid use of the aggregate function. 29 00:01:23,705 --> 00:01:26,300 One of the limitations of built in 30 00:01:26,300 --> 00:01:29,285 aggregate functions, like the average function, 31 00:01:29,285 --> 00:01:31,070 is that they cannot always be 32 00:01:31,070 --> 00:01:33,095 evaluated in the WHERE clause. 33 00:01:33,095 --> 00:01:35,510 So to evaluate a function 34 00:01:35,510 --> 00:01:37,580 like average in the WHERE clause, 35 00:01:37,580 --> 00:01:38,840 we can make use of 36 00:01:38,840 --> 00:01:42,680 a sub-select expression like the one shown here. 37 00:01:42,680 --> 00:01:47,545 Select EMP_ID, F_NAME, L_NAME, 38 00:01:47,545 --> 00:01:50,370 SALARY from employees where SALARY 39 00:01:50,370 --> 00:01:58,060 < ( select AVG (SALARY) from employees). 40 00:01:58,060 --> 00:02:00,410 Notice that the average function is 41 00:02:00,410 --> 00:02:03,410 evaluated in the first part of the sub-query. 42 00:02:03,410 --> 00:02:06,050 Allowing us to circumvent the limitation of 43 00:02:06,050 --> 00:02:09,220 evaluating it directly in the WHERE clause. 44 00:02:09,220 --> 00:02:11,340 The sub-select doesn't just 45 00:02:11,340 --> 00:02:13,250 have to go in the WHERE clause. 46 00:02:13,250 --> 00:02:15,590 It can also go in other parts of 47 00:02:15,590 --> 00:02:19,520 the query, such as in the list of columns to be selected. 48 00:02:19,520 --> 00:02:23,800 Such sub-queries are called column expressions. 49 00:02:23,800 --> 00:02:26,510 Now, let's look at a scenario where 50 00:02:26,510 --> 00:02:29,525 we might want to use a column expression. 51 00:02:29,525 --> 00:02:32,195 Say we wanted to compare the salary of 52 00:02:32,195 --> 00:02:34,835 each employee with the average salary. 53 00:02:34,835 --> 00:02:38,555 We could try a query like select EMP_ID, 54 00:02:38,555 --> 00:02:44,225 SALARY, AVG(SALARY) AS AVG_SALARY from employees. 55 00:02:44,225 --> 00:02:47,930 Running this query will result in an error 56 00:02:47,930 --> 00:02:52,325 indicating that no group by clause is specified. 57 00:02:52,325 --> 00:02:54,860 We can circumvent this error by using 58 00:02:54,860 --> 00:02:56,240 the average function in 59 00:02:56,240 --> 00:02:59,495 a sub-query placed in the list of the columns. 60 00:02:59,495 --> 00:03:05,255 For example, select EMP_ID, SALARY, 61 00:03:05,255 --> 00:03:09,665 (select AVG(SALARY) from employees 62 00:03:09,665 --> 00:03:14,500 ) AS AVG_SALARY from employees. 63 00:03:14,500 --> 00:03:16,340 Another option is to make 64 00:03:16,340 --> 00:03:19,585 the sub-query be part of the FROM clause. 65 00:03:19,585 --> 00:03:22,580 Sub-queries like these are sometimes 66 00:03:22,580 --> 00:03:25,685 called derived tables or table expressions. 67 00:03:25,685 --> 00:03:28,160 Because the outer query uses the results 68 00:03:28,160 --> 00:03:31,220 of the sub-query as a data source. 69 00:03:31,220 --> 00:03:33,815 Let's look at an example to create 70 00:03:33,815 --> 00:03:35,090 a table expression that 71 00:03:35,090 --> 00:03:38,420 contains nonsensitive employee information. 72 00:03:38,420 --> 00:03:42,725 Select * from (select EMP_ID, 73 00:03:42,725 --> 00:03:49,825 F_NAME, L_NAME, DEP_ID from employees) AS EMP4ALL. 74 00:03:49,825 --> 00:03:52,940 The derived table in a sub-query does not 75 00:03:52,940 --> 00:03:56,705 include sensitive fields like date of birth or salary. 76 00:03:56,705 --> 00:03:59,040 This example is a trivial one, and 77 00:03:59,040 --> 00:04:01,040 we could just as easily have 78 00:04:01,040 --> 00:04:03,715 included the columns in the outer query. 79 00:04:03,715 --> 00:04:07,820 However, such derived tables can prove to be powerful in 80 00:04:07,820 --> 00:04:10,370 more complex situations such as when 81 00:04:10,370 --> 00:04:13,835 working with multiple tables and doing joins. 82 00:04:13,835 --> 00:04:16,130 In this video, you have seen how 83 00:04:16,130 --> 00:04:18,470 sub-queries and nested queries can be 84 00:04:18,470 --> 00:04:21,110 used to form richer queries and how they can 85 00:04:21,110 --> 00:04:24,935 overcome some of the limitations of aggregate functions. 86 00:04:24,935 --> 00:04:28,780 You also learned to use sub-queries in the WHERE clause, 87 00:04:28,780 --> 00:04:32,270 in the list of columns and in the FROM clause. 88 00:04:32,270 --> 00:04:35,400 Thanks for watching this video