Pemrosesan Query Join dan Subquery IF 6323903 Sistem

Pemrosesan Query Join dan Subquery IF 6323903 - Sistem Basis Data andika. amalia@ittelkom-pwt. ac. id

Capaian Pembelajaran • Mahasiswa dapat melakukan query data dari beberapa tabel • Mahasiswa dapat melakukan query dengan menggunakan subquery

Join • • • Natural Join Inner Join Outer Join Cross Join Equijoin Self-Join

Natural Join (1) • The NATURAL JOIN clause is based on all columns in the two tables that have the same name. • It selects rows from the two tables that have equal values in all matched columns. • If the columns having the same names have different data types, an error is returned.

Natural Join (2) SELECT department_id, department_name, location_id, city FROM departments NATURAL JOIN locations ;

Inner Join (1) • Retrieves records from multiple tables after comparing values present in a common column. • Retrieves only those rows that satisfy the join condition in the common column. • Syntax: SELECT column_name, column_name [, column_name] FROM table 1_name JOIN table 2_name ON table 1_name. ref_column_name join_operator table 2_name. ref_column_name

Inner Join (2) SELECT e. employee_id, e. last_name, e. department_id, d. location_id FROM employees e JOIN departments d ON (e. department_id = d. department_id);

Outer Join (1) • Displays the result set containing all the rows from one table and the matching rows from another table. • Displays NULL for non-matching records. • Is of the following types: q Left outer join q Right outer join q Full outer join • Syntax: SELECT column_name, column_name [, column_name] FROM table 1_name [LEFT | RIGHT| FULL] OUTER JOIN table 2_name ON table 1_name. ref_column_name join_operator table 2_name. ref_column_name

Contoh DEPARTMENTS EMPLOYEES … There are no employees in department 190.

Outer Join (2) : Left Outer Join SELECT e. last_name, d. department_id, d. department_name FROM employees e LEFT OUTER JOIN departments d ON (e. department_id = d. department_id) ; …

Outer Join (3) : Right Outer Join SELECT e. last_name, d. department_id, d. department_name FROM employees e RIGHT OUTER JOIN departments d ON (e. department_id = d. department_id) ; …

Outer Join (4) : Full Outer Join SELECT e. last_name, d. department_id, d. department_name FROM employees e FULL OUTER JOIN departments d ON (e. department_id = d. department_id) ; …

Cross Join (1) • Merupakan bentuk cartesian product • Menampilkan semua kombinasi dari baris data • Cartesian product is terbentuk saat: • Join condition dihilangkan • Join condition tidak valid • Semua baris pada tabel pertama dijoin dengan semua baris di table kedua • To avoid a Cartesian product, always include a valid join condition

Cross Join (2) • Untuk membuat cartesian product dapat digunakan cross join EMPLOYEES (20 rows) … Cartesian product: 20 x 8 = 160 rows … DEPARTMENTS (8 rows)

Cross Join (3) SELECT last_name, department_name FROM employees CROSS JOIN departments ;

Equijoin • The same as an inner join and joins tables with the help of a foreign key. • Contoh : SELECT * FROM Human. Resources. Employee. Department. History d JOIN Human. Resources. Employee e ON d. Employee. ID = e. Employee. ID JOIN Human. Resources. Department p ON p. Department. ID = d. Department. ID

Self Join (1) • Joins a table with itself. • Correlates one row in a table with other rows in the same table. • Uses a table name twice by giving it two alias names in the query.

Self Join (2) EMPLOYEES (WORKER) EMPLOYEES (MANAGER) MANAGER_ID in the WORKER table is equal to EMPLOYEE_ID in the MANAGER table.

Self Join (3) SELECT e. last_name emp, m. last_name mgr FROM employees e JOIN employees m ON (e. manager_id = m. employee_id);

Question Which one of the following joins is used to correlate one row in a table with other rows in the same table? a. Equi b. Self c. Outer d. Inner Solution: b. Self

Using Subqueries to Solve Problem • Who has a salary greater than Abel’s? Main query: Which employees have salaries greater than Abel’s salary? Subquery: What is Abel’s salary?

Subqueries (1) • Jika anda ingin mengambil data order yang subtotal ordernya lebih besar dari rata-rata subtotal dari semua order ?

Subqueries (2) : Sintaks SELECT FROM WHERE select_list table expr operator (SELECT FROM select_list table); • The subquery (inner query) executes once before the main query (outer query). • The result of the subquery is used by the main query.

Subqueries (3) : Sintaks • A subquery: – Is an SQL statement that is used within another SQL statement. – Is nested inside the WHERE or HAVING clause of the SELECT, INSERT, UPDATE, and DELETE statements. – For example: SELECT * FROM Employee. Details WHERE Designation = (SELECT Designation FROM Employee. Details WHERE Emp. Name = 'John') Represents the parent query and is called an outer query. Represents the subquery and is called an inner query.

Subqueries (4) : Sintaks SELECT FROM GROUP BY HAVING department_id, MIN(salary) employees department_id MIN(salary) > (SELECT MIN(salary) FROM employees WHERE department_id = 50);

Subqueries (5) : Contoh SELECT last_name, salary FROM employees WHERE salary > (SELECT salary FROM employees WHERE last_name = 'Abel');

Latihan 1. Menampilkan sales. Order. ID yang subtotalnya lebih besar dari subtotal Sales. Order. Id = ‘ 43661’ 2. Tampilkan ID Pegawai yang sudah menikah dan memiliki Title sama dengan ID Pegawai = 16

Subqueries (6) : Tipe Subquery – Single-row subquery Main query Subquery returns ST_CLERK – Multiple-row subquery Main query Subquery returns ST_CLERK SA_MAN

Subqueries (7) : Operator pada Single -Row Query Operator = Meaning Equal to > Greater than >= Greater than or equal to < Less than <= Less than or equal to <> Not equal to

Subqueries (8) : In and Exists Keyword • You can specify different kinds of conditions on subqueries by using the following keywords: – IN – EXISTS

Subqueries (9) : In Keyword – IN keyword: – Is used to retrieve rows in a subquery based on the match of values given in a list. – Syntax: • SELECT column, column [, column] FROM table_name WHERE column [ NOT ] IN SELECT column FROM table_name [WHERE conditional_expression] ) – For example: • SELECT Employee. ID FROM Human. Resources. Employee. Address WHERE Address. ID IN (SELECT Address. ID FROM Person. Address WHERE City = 'Bothell') Retrieves the Employee. ID attribute of all the employees who live in Bothell, from the Employee. Address table. (

Subqueries (10) : Exists Keyword – EXISTS keyword: – Is used to check the existence of the data and returns true or false. – Syntax: • SELECT column, column [, column] FROM table_name WHERE EXISTS ( SELECT column FROM table_name [ WHERE conditional_expression] ) – For example: • SELECT Employee. ID, Title FROM Human. Resources. Employee WHERE EXISTS (SELECT * FROM Human. Resources. Employee. Department. History WHERE Employee. ID = Human. Resources. Employee. ID AND Department. ID = 4)

Modified Comparison Operators (1) • The ALL keyword: – Returns TRUE, if all the values retrieved by the subquery satisfy the comparison operator. • The ANY keyword: – Returns TRUE, if any value retrieved by the subquery satisfies the comparison operator.

Modified Comparison Operators (2) • The following table shows the operators that can be used with the ALL and ANY keywords. Operator >ALL Description Means greater than the maximum value in the list. >ANY Means greater than the minimum value in the list. The expression | column_name >ANY (10, 20, 30) means ‘greater than 10’. Means any of the values in the list. It acts in the same way as the IN clause. The expression | column_name =ANY (10, 20, 30) means ‘equal to either 10 or 20 or 30’. Means not equal to any value in the list. The expression | column_name <>ANY (10, 20, 30) means ‘not equal to 10 or 20 or 30’. Means not equal to all the values in the list. It acts in the same way as the NOT IN clause. The expression | column_name <>ALL (10, 20, 30) means ‘not equal to 10 and 20 and 30’. =ANY <>ALL

Modified Comparison Operators (3) • Example : SELECT Employee. ID, Title FROM Human. Resources. Employee WHERE Vacation. Hours >ALL (SELECT Vacation. Hours FROM Human. Reso urces. Emp loyee WHERE Title = 'Recruite r')

Multiple Row Subquery SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary < ANY (SELECT salary FROM employees WHERE job_id = 'IT_PROG') AND job_id <> 'IT_PROG';

Question • Which one of the following keywords is used to match a column value with any of the values in the list returned by the inner query? a. b. c. d. EXISTS HAVING WHERE IN • Solution: d. IN

Menggunakan Agregat Functions • Aggregate functions: – Can be used to generate aggregate values from the inner query. – For example: SELECT Employee. Id FROM Human. Resources. Employee WHERE Vacation. Hours >(SELECT AVG(Vacation. Hours) FROM Human. Resources. Employee WHERE Title = 'Marketing Assistant') The inner query returns the average vacation hours of all the employees who are titled as Marketing Assistant.

Nested Subquery

Nested Subquery • Nested subqueries: – Can contain one or more subqueries. – Can be used when the condition of a query is dependent on the result of another query. – For example: SELECT Department. ID FROM Human. Resources. Employee. Department. History WHERE Employee. ID = (SELECT Employee. ID FROM Human. Resources. Employee WHERE Contact. ID = (SELECT Contact. ID FROM Person. Contact WHERE Email. Address = 'taylor 0@adventure-works. com') ) The level 1 inner query uses this Contact. ID to search for the Employee. ID of the employee with the given e-mail address. The level 2 inner query returns the Contact. ID of an employee based on the e-mail address of the employee from the Person table.

Correlated Subquery • Correlated subquery: – Is a query that depends on the outer query for its evaluation. – Uses the WHERE clause to refer to the table specified in the FROM clause. – For example: SELECT * FROM Employee. Details e WHERE Salary = (SELECT max(Salary) FROM Employee. Details WHERE Dept. No = e. Dept. No) Displays the details of employees who earn the highest salary in their department.

Apply Operator • The APPLY operator: – Combines the result sets of two queries such that for each row of the first query, the second query is evaluated to determine if any rows are returned. • The two types of APPLY operators supported by SQL Server are: – CROSS APPLY – OUTER APPLY • The syntax for using the APPLY operator is: left_table_expression {CROSS | OUTER} APPLY right_table_expression

Cross Operator • The CROSS APPLY operator: – Returns only those rows from the outer result set that matches with the inner result set. – For example: SELECT d. Customer_name, d. Acc_num, br. Loan_num FROM Depositor d CROSS APPLY (SELECT * FROM Borrower b WHERE d. Customer_name = b. Customer_name) br Displays the name, account number, and loan number of the customers who are borrowers as well as depositors.

Outer Operator • The OUTER APPLY operator: o Returns all rows from the outer result set even if the corresponding row is not found in the inner result set. o Contains NULL values when the inner result set does not return a matching value. o For example: SELECT d. Customer_name, d. Acc_num, br. Loan_num FROM Depositor d OUTER APPLY (SELECT * FROM Borrower b WHERE d. Customer_name = b. Customer_name)br Displays the details of the customers who are depositors, along with the loan number of the customers who are borrowers as well.

COMBINE OR COMPARE OPERATOR • At times, if you want to view data from the multiple tables together, you need to combine or compare the result sets. • SQL Server provides the various operators, such as UNION, EXCEPT, and INTERSECT, to combine as well as compare the result sets.

Combining Result Sets – SQL Server provides the UNION operator to combine data from multiple tables. – You can use the EXCEPT and INTERSECT operators to exclude records from the result sets based on different criteria. – The basic rules to follow while using the UNION, EXCEPT, and INTERSECT operators are: – The number and the sequence of the columns must be the same in all queries. – The data types of the columns in all the queries must be compatible.

Combining Result Sets (Contd. ) – The UNION operator is used to combine data from multiple tables. – The syntax of the UNION operator is: – – – <query_expression> UNION [ ALL ] <query_expression> [. . . n ] ] – For example: – – – SELECT Customer_name FROM Depositor UNION SELECT Customer_name FROM Borrower Displays the details of all customers who are either depositors or borrowers.

Combining Result Sets (Contd. ) • The EXCEPT operator: • Compares two result sets and returns the data from the first result set that is not found in the second result set. • For example: SELECT Customer_name FROM Depositor EXCEPT Select Customer_name FROM Borrower Displays the name of those customers who are depositors but not borrowers.

Combining Result Sets (Contd. ) • The INTERSECT operator: • Returns the common rows after comparing two result sets. If the common rows are not found, then NULL value is returned. • For example: SELECT Customer_name FROM Depositor INTERSECT SELECT Customer_name FROM Borrower Displays the name of those customers who are depositors as well as have taken loan from the bank.

Combining Result Sets (Contd. ) – Click the following link to view an animation on combining result sets.

Soal • Which one of the following operators returns only those rows from the outer result set that match with the inner result set? q q CROSS APPLY OUTER APPLY UNION EXCEPT • Solution: q CROSS APPLY

Working with Temporary Result Sets (Using Common Table Expression : CTE) • The result sets returned after executing the queries are known as temporary result sets. • The temporary result sets are not stored in the database and remain in the memory till the execution of the query. • After the execution of the query, the table containing the temporary result set is automatically dropped. • SQL Server allows you to access the temporary result sets by using CTEs. • A CTE is used to create a temporary named result set. It is defined within the execution scope of a single SQL statement.

Working with Temporary Result Sets (Contd. ) • The syntax of CTE is: WITH expression_name [(column_name 1 [, . . . n] )] AS (CTE_Query_defnition) • For example: WITH Rate. CTE(Rate) AS ( SELECT TOP 10 Rate = Rate FROM Human. Resources. Employee. Pay. History ) SELECT Rate, Max_Rate = (SELECT max(Rate) FROM Rate. CTE Displays the top 10 rates of employees along with the maximum rate.

Working with Temporary Result Sets (Contd. ) • A recursive CTE references a CTE multiple times in the same query. • In a recursive CTE, result sets of more than one query are combined to populate the CTE. • The recursive CTE consists of: q Anchor query q Recursive query Includes one or more query expressions joined by Includes one or more query joined by the UNION ALL operator thatexpressions uses the CTE itself the UNION the ALL, UNION, EXCEPT, or to produce result. INTERSECT operator.

Working with Temporary Result Sets (Contd. ) • The syntax of recursive CTE is: WITH expression_name [(column_name 1 [, . . . n] )] AS ( (CTE_Query_defnition) UNION ALL (Recursive_Query_Expression) Represents the recursive query. Represents the anchor query that is used as an input for the recursive query.

Working with Temporary Result Sets (Contd. ) • The process of execution of a recursive CTE is: 1. The anchor query is executed first to create the first invocation or base result set (T 0). 2. The recursive query is executed with Ti as an input and Ti+1 as an output. Here, i starts from 0. 3. Step 2 is repeated until an empty set is returned. 4. The final result set is displayed. This result set is a UNION ALL of T 0 to Tn.

Derived Table • Sometimes, obtaining data in a result set is a complicated process as it might include some complex aggregate calculations, involving fields of one or more tables. • In such a cases, you may need to simplify the process by using temporary tables or views. • The derived table is a virtual table, created by using a SELECT statement and is given an alias name using the AS clause. • The scope for the derived tables is restricted within the query, can be used only within the query, and ceases to exist once the query has finished executing.

Working with Derived Tables (Contd. ) • For example: SELECT Table 1. Productid, Total. Quantity. Ordered, Total. Sales. Orders. Placed FROM (SELECT Product. ID, SUM(Order. Qty) AS Total. Quantity. Ordered FROM Sales. Order. Detail GROUP BY Product. Id )AS Table 1 INNER JOIN (SELECT Product. ID, SUM(Order. Qty) AS Total. Sales. Orders. Placed FROM Sales. Order. Detail GROUP BY Product. Id , Sales. Order. Id ) AS Table 2 ON Table 1. Product. ID=Table 2. Product. ID

Working with Derived Tables (Contd. ) • The following steps describe the execution of the preceding statements: 1. The FROM clause of the preceding statement creates two derived tables named, Table 1 and Table 2. 2. The derived table, Table 1, selects the total number of orders placed for each product from the Sales. Order. Detail table. 3. The derived table, Table 2, selects the total sales orders placed for each product from the Sales. Order. Detail table. 4. Finally, an inner join is used to obtain data from both the derived tables into a common result set.

Demo: Using CTE • Problem Statement: Adventure. Works, Inc is a leading manufacturing company. The company stores the details of sold products in the Transaction. History table. The management of the company is reviewing the performance of its products in the market. Therefore, the management wants to know the number of units sold of each product. They also want to identify the product that sold the maximum units. How will you perform this task? (use CTE)