{"id":10492,"date":"2019-07-18T08:19:43","date_gmt":"2019-07-18T08:19:43","guid":{"rendered":"https:\/\/ittutorial.org\/?p=10492"},"modified":"2019-07-18T10:30:38","modified_gmt":"2019-07-18T10:30:38","slug":"oracle-sql-tutorials-chapter-4","status":"publish","type":"post","link":"https:\/\/ittutorial.org\/oracle-sql-tutorials-chapter-4\/","title":{"rendered":"Oracle SQL Tutorials \u2013 Chapter 5"},"content":{"rendered":"<p><strong>Chapter 5 \u2013 Selecting Data From Multiple Tables<\/strong><\/p>\n<ul>\n<li>Usage of multiple tables in one query<\/li>\n<li>Combining tables &#8211; JOIN<\/li>\n<li>Different Types of SQL Join<\/li>\n<li>Left Outer Join<\/li>\n<li>Right Outer Join<\/li>\n<li>SET Operators<\/li>\n<li>Subqueries<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p><strong>Combining Tables &#8211; JOIN<\/strong><\/p>\n<ul>\n<li>JOIN is used to select data from multiple tables.<\/li>\n<li>ALIAS is very important in JOIN operations. It will make our job eaiser.<\/li>\n<li>The relation between the tables should be well established.<\/li>\n<li>We must avoid to select unnecessary columns. ( * ) should not be used in queries that are not required.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"946\" height=\"707\" class=\"wp-image-10493\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-93.png\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-93.png 946w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-93-300x224.png 300w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-93-768x574.png 768w\" sizes=\"auto, (max-width: 946px) 100vw, 946px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><strong>INNER JOIN<\/strong><\/p>\n<ul>\n<li>Let\u2019s write an example of \u201c INNER JOIN \u201d.<\/li>\n<\/ul>\n<pre>SELECT department_id, department_name, city\r\n\r\nFROM hr.departments d, hr.locations l\r\n\r\nWHERE d.location_id=l.location_id;<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"356\" height=\"171\" class=\"wp-image-10494\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-94.png\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-94.png 356w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-94-300x144.png 300w\" sizes=\"auto, (max-width: 356px) 100vw, 356px\" \/><\/p>\n<p>&nbsp;<\/p>\n<ul>\n<li>Let\u2019s list the Department\u2019s city.<\/li>\n<\/ul>\n<pre>SELECT department_id, department_name, city\r\n\r\nFROM hr.departments d\r\n\r\nINNER JOIN hr.locations l ON d.location_id=l.location_id;\r\n\r\nOR\r\n\r\nSELECT department_id, department_name, city\r\n\r\nFROM hr.departments d\r\n\r\nJOIN hr.locations l USING (location_id);<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"357\" height=\"173\" class=\"wp-image-10495\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-95.png\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-95.png 357w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-95-300x145.png 300w\" sizes=\"auto, (max-width: 357px) 100vw, 357px\" \/><\/p>\n<p>&nbsp;<\/p>\n<ul>\n<li>Let\u2019s list the ID, name, surname, city and department of employees.<\/li>\n<\/ul>\n<pre>SELECT employee_id, first_name,last_name,city, department_name\r\n\r\nFROM hr.employees e\r\n\r\nJOIN hr.departments d ON d.department_id = e.department_id\r\n\r\nJOIN hr.locations l ON d.location_id = l.location_id;<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"493\" height=\"171\" class=\"wp-image-10496\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-96.png\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-96.png 493w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-96-300x104.png 300w\" sizes=\"auto, (max-width: 493px) 100vw, 493px\" \/><\/p>\n<p>&nbsp;<\/p>\n<ul>\n<li>Let\u2019s write another example.<\/li>\n<\/ul>\n<pre>SELECT e.first_name,e.last_name, e.salary, j.job_title\r\n\r\nFROM hr.employees e\r\n\r\nJOIN hr.jobs j ON e.salary\r\n\r\nBETWEEN j.min_salary AND j.max_salary;<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"281\" height=\"152\" class=\"wp-image-10497\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-97.png\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><strong>LEFT OUTER JOIN<\/strong><\/p>\n<ul>\n<li>Let\u2019s list the departments of employees.<\/li>\n<\/ul>\n<pre>SELECT first_name,e.last_name, e.department_id, d.department_name\r\n\r\nFROM hr.employees e\r\n\r\nLEFT OUTER JOIN hr.departments d ON (e.department_id = d.department_id) ;<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"388\" height=\"151\" class=\"wp-image-10498\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-98.png\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-98.png 388w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-98-300x117.png 300w\" sizes=\"auto, (max-width: 388px) 100vw, 388px\" \/><\/p>\n<p>&nbsp;<\/p>\n<ul>\n<li>Let\u2019s list the departments of employees and non-employed departments.<\/li>\n<\/ul>\n<pre>SELECT first_name,e.last_name, e.department_id, d.department_name\r\n\r\nFROM hr.employees e\r\n\r\nRIGHT OUTER JOIN hr.departments d ON (e.department_id = d.department_id) ;<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"390\" height=\"172\" class=\"wp-image-10499\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-99.png\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-99.png 390w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-99-300x132.png 300w\" sizes=\"auto, (max-width: 390px) 100vw, 390px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><strong>CARTESIAN PRODUCT<\/strong><\/p>\n<ul>\n<li>Cartesian product is mentioned, in case of where the join condition between two tables is not defined.<\/li>\n<li>It fetchs all records from the table on the right for each record of the table on the left.<\/li>\n<\/ul>\n<p>SELECT COUNT(*)\u00a0FROM hr.regions ;\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 SELECT COUNT(*)\u00a0FROM hr.locations ;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"69\" height=\"47\" class=\"wp-image-10500\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-100.png\" \/>\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0<img loading=\"lazy\" decoding=\"async\" width=\"69\" height=\"46\" class=\"wp-image-10501\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-101.png\" \/><\/p>\n<p>&nbsp;<\/p>\n<pre>SELECT *\r\n\r\nFROM hr.regions, hr.locations ;<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"903\" height=\"233\" class=\"wp-image-10502\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-102.png\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-102.png 903w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-102-300x77.png 300w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-102-768x198.png 768w\" sizes=\"auto, (max-width: 903px) 100vw, 903px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><strong>SET OPERATORS<\/strong><\/p>\n<ul>\n<li>SET operators are used to merge multiple tables and queries.<\/li>\n<li>There are 4 types of SET operators. These are;<\/li>\n<li>UNION<\/li>\n<li>UNION ALL<\/li>\n<li>INTERSECT<\/li>\n<li>MINUS<\/li>\n<\/ul>\n<p><strong>UNION OPERATOR<\/strong><\/p>\n<ul>\n<li>It combines data from two datasets but doesn\u2019t return the same records.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-10503\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-103.png\" width=\"271\" height=\"168\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-103.png 632w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-103-300x186.png 300w\" sizes=\"auto, (max-width: 271px) 100vw, 271px\" \/><\/p>\n<p>&nbsp;<\/p>\n<ul>\n<li>Here is an example of UNION operator.<\/li>\n<\/ul>\n<pre>SELECT employee_id, job_id\r\n\r\nFROM hr.employees\r\n\r\nUNION\r\n\r\nSELECT employee_id, job_id\r\n\r\nFROM hr.job_history;<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"174\" height=\"172\" class=\"wp-image-10504\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-104.png\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><strong>UNION ALL OPERATOR<\/strong><\/p>\n<ul>\n<li>It combines data from two datasets and it also returns the same records.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-10505\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-105.png\" width=\"271\" height=\"170\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-105.png 615w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-105-300x188.png 300w\" sizes=\"auto, (max-width: 271px) 100vw, 271px\" \/><\/p>\n<p>&nbsp;<\/p>\n<ul>\n<li>Let\u2019s write an example of UNION ALL operator.<\/li>\n<\/ul>\n<pre>SELECT employee_id, job_id, department_id\r\n\r\nFROM hr.employees\r\n\r\nUNION ALL\r\n\r\nSELECT employee_id, job_id, department_id\r\n\r\nFROM hr.job_history ;<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"271\" height=\"174\" class=\"wp-image-10506\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-106.png\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><strong>INTERSECT OPERATOR<\/strong><\/p>\n<ul>\n<li>It finds the intersection of two datasets.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-10507\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-107.png\" width=\"271\" height=\"179\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-107.png 600w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-107-300x198.png 300w\" sizes=\"auto, (max-width: 271px) 100vw, 271px\" \/><\/p>\n<ul>\n<li>Let\u2019s find the employees which salary is between 3000 and 5000 with intersect operator.<\/li>\n<\/ul>\n<pre>SELECT first_name,last_name,salary FROM hr.employees\r\n\r\nWHERE salary BETWEEN 1000 AND 5000\r\n\r\nINTERSECT\r\n\r\nSELECT first_name,last_name,salary FROM hr.employees\r\n\r\nWHERE salary BETWEEN 3000 AND 7500;<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"212\" height=\"173\" class=\"wp-image-10508\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-108.png\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><strong>MINUS OPERATOR<\/strong><\/p>\n<ul>\n<li>This operator returns the difference between two datasets.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-10509\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-109.png\" width=\"270\" height=\"166\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-109.png 578w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-109-300x185.png 300w\" sizes=\"auto, (max-width: 270px) 100vw, 270px\" \/><\/p>\n<p>&nbsp;<\/p>\n<ul>\n<li>Let\u2019s list the difference between the records in job_history table and records in employees table.<\/li>\n<\/ul>\n<pre>SELECT employee_id, job_id\r\n\r\nFROM hr.employees\r\n\r\nMINUS\r\n\r\nSELECT employee_id, job_id\r\n\r\nFROM hr.job_history;<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"166\" height=\"173\" class=\"wp-image-10510\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-110.png\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><strong>SUB QUERIES<\/strong><\/p>\n<ul>\n<li>Sometimes,the result of one query, must be used in another query. In these situaations we use subqueries.<\/li>\n<li>Subquery runs before the main query.<\/li>\n<li>The result of the subquery is used by the main query.<\/li>\n<li>Subquries make it easy to find the desired result.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-10511\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-111.png\" width=\"292\" height=\"97\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-111.png 520w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-111-300x100.png 300w\" sizes=\"auto, (max-width: 292px) 100vw, 292px\" \/><\/p>\n<p>&nbsp;<\/p>\n<ul>\n<li>Let\u2019s list the employees, whose salary is more than \u2018Abel\u2019s salary.<\/li>\n<\/ul>\n<pre>SELECT first_name,last_name, salary\r\n\r\nFROM hr.employees\r\n\r\nWHERE salary &gt;\r\n\r\n(SELECT salary\r\n\r\nFROM hr.employees\r\n\r\nWHERE last_name = 'Abel');<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"211\" height=\"172\" class=\"wp-image-10512\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-112.png\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><strong>SUBQUERY VARIATIONS<\/strong><\/p>\n<ul>\n<li><strong>Single-Row Subqueries<\/strong><\/li>\n<li><strong>Multi-Row Subqueries<\/strong><\/li>\n<\/ul>\n<p><strong>SINGLE-ROW SUBQUERIES<\/strong><\/p>\n<ul>\n<li>Single-row subquery returns one row.<\/li>\n<li>They are used with =, &lt;, &gt;, &lt;=, &gt;=, &lt;&gt; operators.<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<ul>\n<li>Let\u2019s list the employees which salary is more than \u2018Austin\u2019s salary and in same department with \u2018Austin\u2019.<\/li>\n<\/ul>\n<pre>SELECT first_name,last_name, job_id, salary\r\n\r\nFROM hr.employees\r\n\r\nWHERE job_id =\r\n\r\n(SELECT job_id\r\n\r\nFROM hr.employees\r\n\r\nWHERE last_name = 'Austin')\r\n\r\nAND salary &gt;\r\n\r\n(SELECT salary\r\n\r\nFROM hr.employees\r\n\r\nWHERE last_name = 'Austin');<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"267\" height=\"69\" class=\"wp-image-10513\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-113.png\" \/><\/p>\n<p>&nbsp;<\/p>\n<ul>\n<li>Group functions can be used with single-row functions.<\/li>\n<li>Let\u2019s list the employee who has the lowest salary.<\/li>\n<\/ul>\n<pre>SELECT first_name,last_name, job_id, salary\r\n\r\nFROM hr.employees\r\n\r\nWHERE salary =\r\n\r\n(SELECT MIN(salary)\r\n\r\nFROM hr.employees);<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"276\" height=\"51\" class=\"wp-image-10514\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-114.png\" \/><\/p>\n<p>&nbsp;<\/p>\n<ul>\n<li>Usage of \u2018HAVING\u2019 with single-row functions<\/li>\n<li>Let\u2019s list the employees which salaries are higher than the employee which has the minimum salary in department 50.<\/li>\n<\/ul>\n<pre>SELECT d.department_name,e.department_id, MIN(salary)\r\n\r\nFROM hr.employees e,hr.departments d where e.department_id=d.department_id\r\n\r\nGROUP BY e.department_id,d.department_name\r\n\r\nHAVING MIN(salary) &gt;\r\n\r\n(SELECT MIN(salary)\r\n\r\nFROM hr.employees\r\n\r\nWHERE department_id = 50);<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"316\" height=\"172\" class=\"wp-image-10515\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-115.png\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-115.png 316w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-115-300x163.png 300w\" sizes=\"auto, (max-width: 316px) 100vw, 316px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><strong>MULTI-ROW SUBQUERIES<\/strong><\/p>\n<ul>\n<li>Multi-row subqery returns one or more rows.<\/li>\n<li>We use them with multi-row comparison operators ( <strong>IN, ANY, ALL<\/strong>)<\/li>\n<\/ul>\n<p><strong>IN OPERATOR<\/strong><\/p>\n<ul>\n<li>It checks if the value is exist in the subquery.<\/li>\n<\/ul>\n<pre>SELECT employee_id, first_name,last_name,salary\r\n\r\nFROM hr.employees\r\n\r\nWHERE salary in\r\n\r\n(SELECT MIN(salary)\r\n\r\nFROM hr.employees\r\n\r\nGROUP BY department_id);<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"296\" height=\"172\" class=\"wp-image-10516\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-116.png\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><strong>ANY OPERATOR<\/strong><\/p>\n<ul>\n<li>If any of the subquery values meet the condition, the ANY operator returns true.<\/li>\n<li>This operator is used with any of the \u2018 =, &lt;, &gt; \u2019 operators.<\/li>\n<li>\u201c =ANY \u201c means ; IN operator.<\/li>\n<li>\u201c &lt;ANY \u201c means ; smaller than the biggest.<\/li>\n<li>\u201c &gt;ANY \u201c means ; bigger than the smallest.<\/li>\n<li>Let\u2019s list the employees which has less salary than the employee which salary is the biggest in the IT_PROG position.<\/li>\n<\/ul>\n<pre>SELECT employee_id, last_name, job_id, salary\r\n\r\nFROM hr.employees\r\n\r\nWHERE salary &lt; ANY\r\n\r\n(SELECT salary\r\n\r\nFROM hr.employees\r\n\r\nWHERE job_id = 'IT_PROG')\r\n\r\nAND job_id &lt;&gt; 'IT_PROG';<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"281\" height=\"174\" class=\"wp-image-10517\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-117.png\" \/><\/p>\n<p>&nbsp;<\/p>\n<ul>\n<li>Let\u2019s list the employees which has more salary than the employee which salary is the lowest in the IT_PROG position.<\/li>\n<\/ul>\n<pre>SELECT employee_id, last_name, job_id, salary\r\n\r\nFROM hr.employees\r\n\r\nWHERE salary &gt; ANY\r\n\r\n(SELECT salary\r\n\r\nFROM hr.employees\r\n\r\nWHERE job_id = 'IT_PROG')\r\n\r\nAND job_id &lt;&gt; 'IT_PROG';<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"302\" height=\"174\" class=\"wp-image-10518\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-118.png\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-118.png 302w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-118-300x173.png 300w\" sizes=\"auto, (max-width: 302px) 100vw, 302px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><strong>ALL OPERATOR<\/strong><\/p>\n<ul>\n<li>If all of the subquery values meet the condition, the ALL operator returns true.<\/li>\n<li>&gt;ALL means ; bigger than the biggest.<\/li>\n<li>&lt;ALL means ; smaller than the smallest.<\/li>\n<li>Let\u2019s list the employees which salary is smaller than the smallest salary in \u2018IT_PROG\u2019 position.<\/li>\n<\/ul>\n<pre>SELECT employee_id, first_name, last_name, job_id, salary\r\n\r\nFROM hr.employees\r\n\r\nWHERE salary &lt; ALL\r\n\r\n(SELECT salary\r\n\r\nFROM hr.employees\r\n\r\nWHERE job_id = 'IT_PROG')\r\n\r\nAND job_id &lt;&gt; 'IT_PROG';<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"361\" height=\"173\" class=\"wp-image-10519\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-119.png\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-119.png 361w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-119-300x144.png 300w\" sizes=\"auto, (max-width: 361px) 100vw, 361px\" \/><\/p>\n<p>&nbsp;<\/p>\n<ul>\n<li>Let\u2019s list the employees which salary is greater than the biggest salary in \u2018IT_PROG\u2019 position.<\/li>\n<\/ul>\n<pre>SELECT employee_id, first_name, last_name, job_id, salary\r\n\r\nFROM hr.employees\r\n\r\nWHERE salary &gt; ALL\r\n\r\n(SELECT salary\r\n\r\nFROM hr.employees\r\n\r\nWHERE job_id = 'IT_PROG')\r\n\r\nAND job_id &lt;&gt; 'IT_PROG';<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"354\" height=\"171\" class=\"wp-image-10520\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-120.png\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-120.png 354w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-120-300x145.png 300w\" sizes=\"auto, (max-width: 354px) 100vw, 354px\" \/><\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Chapter 5 \u2013 Selecting Data From Multiple Tables Usage of multiple tables in one query Combining tables &#8211; JOIN Different Types of SQL Join Left Outer Join Right Outer Join SET Operators Subqueries &nbsp; Combining Tables &#8211; JOIN JOIN is used to select data from multiple tables. ALIAS is very important in JOIN operations. It &hellip;<\/p>\n","protected":false},"author":29,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[3019],"tags":[3212,3009,3048,3010,3198,1340,3001,3013,3215,3216,3202,3014,1572,3206,3208,3207,3209,3205,3003,3210,3211,3204,3017,3016,3199,3200,3214,3213,3201,3000,3012,3203,3002,3008],"class_list":["post-10492","post","type-post","status-publish","format-standard","","category-oracle-sql","tag-desc-syntax","tag-how-to-write-sql","tag-learn-oracle","tag-learn-sql","tag-operator-precedence","tag-oracle-sql","tag-oracle-sql-tutorial","tag-oracle-sql-tutorials","tag-order-by-asc","tag-order-by-desc","tag-order-by-syntax","tag-rules-of-sql","tag-sql","tag-sql-asc","tag-sql-asc-syntax","tag-sql-ascending","tag-sql-ascending-syntax","tag-sql-assigning-variable","tag-sql-commands","tag-sql-desc","tag-sql-desc-syntax","tag-sql-how-to-assign-variable","tag-sql-komutlari","tag-sql-ogren","tag-sql-operator-precedence","tag-sql-order-by","tag-sql-order-by-desc","tag-sql-ordey-by-asc","tag-sql-ordey-by-syntax","tag-sql-tutorial","tag-sql-tutorials","tag-sql-variable","tag-what-is-sql","tag-writing-sql"],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/10492","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/users\/29"}],"replies":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/comments?post=10492"}],"version-history":[{"count":5,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/10492\/revisions"}],"predecessor-version":[{"id":10525,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/10492\/revisions\/10525"}],"wp:attachment":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media?parent=10492"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/categories?post=10492"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/tags?post=10492"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}