Oracle SQL Tutorial -5 Getting Data From More Than One Tables

Hi ,

in this article I will tell you about the joins . We use Join to join two tables .The JOIN-related column values must be in two tables .

JOINING TABLES – JOIN

• Unnecessary columns should be prevented from coming, and we should not use * when using the SELECT statement unless it is required .

• We need to pay attention to the use of Alias because we combine more than one table if we do not use alias we can produce incorrect results . At the same time , we avoid column uncertainties by using alias again .

INNER JOIN

SELECT * FROM SH.CUSTOMERS a INNER JOIN SH.SALES b ON a.CUST_ID=b.CUST_ID;

• Depending on the relationship between specific columns of two or more tables , data is drawn from these tables and this is called join .

• In the example you see above, there is a cust_id column relationship between the customers table and the sales table that we used throughout the entire post . We used inner join to extract data from these two tables . (If we do not specify the join type with words like inner , right , left in the query , inner join is the default . )

LEFT OUTER JOIN

• As you can see , all the records came from the customers on the left and only the intersecting records came from the table on the right .

SELECT a.CUST_FIRST_NAME,a.CUST_LAST_NAME,a.CUST_ID FROM SH.CUSTOMERS a LEFT OUTER JOIN SH.SALES b ON (a.CUST_ID=b.CUST_ID);

RIGHT OUTER JOIN

• Left and right are statements that specify whether the table on which to select all records is on the right or left .

SELECT a.CUST_FIRST_NAME,a.CUST_LAST_NAME,a.CUST_ID FROM SH.CUSTOMERS a RIGHT OUTER JOIN SH.SALES b ON (a.CUST_ID=b.CUST_ID);

CARTRESSIAN CRASH

• When a join condition is invalid and all rows in the first table are combined with all rows in the second table , a cartesian product is formed .

SELECT * FROM ADMIN.NEW_TABLE,ADMIN.OGRENCI;

SET OPERATORS

• Used operators to merge multiple tables and queries . We have 4 set operators : MINUS , UNION , UNION ALL and INTERSECT .

UNION OPERATOR

• It serves to merge data from two data sets but does not take records of the same value .

SELECT PROMO_ID FROM SH.PROMOTIONS UNION SELECT PROMO_ID FROM SH.SALES;

UNION ALL OPERATOR

• It merges the data in the two data sets and also fills the records with the same value.

SELECT PROD_ID,TIME_ID,PROMO_ID FROM SH.COSTS UNION ALL SELECT PROD_ID,TIME_ID,PROMO_ID FROM SH.SALES;

INTERSECT OPERATOR

• Allows us to find the intersection set of data in two data sets .

SELECT CUST_FIRST_NAME,CUST_LAST_NAME,CUST_CREDIT_LIMIT FROM SH.CUSTOMERS WHERE CUST_CREDIT_LIMIT BETWEEN 1500 AND 5000 INTERSECT SELECT CUST_FIRST_NAME,CUST_LAST_NAME,CUST_CREDIT_LIMIT FROM SH.CUSTOMERS WHERE CUST_CREDIT_LIMIT BETWEEN 3000 AND 10000;

MINUS OPERATOR

• Used to indicate the difference between the data in the two data sets . (for example , to show what is in a set but not in b , and vice versa . )

SELECT CUST_ID FROM SH.CUSTOMERS MINUS SELECT CUST_ID FROM SH.SALES;

SUB Queries

• The value obtained from one query may be of interest to the other query . The sub (internal) query runs before the main (external) query . The result of the subquery is used by the main query . Subqueries generally help to make main queries easier and more understandable .

SELECT FIRST_NAME,LAST_NAME,SALARY FROM ADMIN.NEW_TABLE WHERE SALARY > (SELECT SALARY FROM ADMIN.NEW_TABLE WHERE LAST_NAME='Nabi');

SINGLE LINE SUB QUOTES –1

• Returns a single row .

• Usually (= , < , >= , <>) used with operators .

SELECT FIRST_NAME,LAST_NAME,JOB_ID,SALARY FROM ADMIN.NEW_TABLE WHERE JOB_ID=(SELECT JOB_ID FROM ADMIN.NEW_TABLE WHERE LAST_NAME='Nabi') AND SALARY < (SELECT SALARY FROM ADMIN.NEW_TABLE WHERE LAST_NAME='Nabi');

SINGLE LINE SUB QUOTES –2

• Group functions can be used in these queries .

SELECT CUST_FIRST_NAME,CUST_LAST_NAME,CUST_YEAR_OF_BIRTH FROM SH.CUSTOMERS WHERE CUST_YEAR_OF BIRTH = (SELECT MIN(CUST_YEAR_OF_BIRTH) FROM SH.CUSTOMERS);

SINGLE LINE SUB QUOTES –3

• We use these queries together with HAVING .

MULTI LINE SUB QUOTES

• They have multiple rows and are used with multiple row comparison operators .

IN OPERATOR

• Used when querying whether the values in the list are equal to any of them .

SELECT CUST_ID,CUST_FIRST_NAME,CUST_LAST_NAME,CUST_CREDIT_LIMIT FROM SH.CUSTOMERS WHERE CUST_CREDIT_LIMIT IN (SELECT MIN(CUST_CREDIT_LIMIT) FROM SH.CUSTOMERS GROUP BY CUST_ID);

ANY OPERATOR

• We use the ANY operator to compare each value generated by the subquery to a value specified in the main query .

• The ANY operator is used with expressions such as “= , < , >” and the expression “ = ANY” is equivalent to the IN operator .

• <ANY means ” smaller than the largest ” > ANY means ” greater than the smallest ” .

SELECT CUST_ID,CUST_LAST_NAME,CUST_CREDIT_LIMIT,COUNTRY_ID FROM SH.CUSTOMERS WHERE CUST_CREDIT_LIMIT < ANY (SELECT CUST_CREDIT_LIMIT FROM SH.CUSTOMERS WHERE COUNTRY_ID=52770);

• In the example above , I wanted to show how we use and use the ANY operator to find out people with lower credit than the person with the highest credit limit of the 52770 country_id people .

ALL OPERATOR

• If we want each value generated by the subquery to be used as input for the main query , we use the ALL operator . > ALL is defined as the expression “ larger than the largest expression ”, <ALL is the expression “smaller than the smallest expression ” .

Our example for the All operator :

SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,JOB_ID,SALARY FROM ADMIN.NEW_TABLE WHERE SALARY < ALL (SELECT SALARY FROM ADMIN.NEW_TABLE WHERE FIRST_NAME='Muzaffer') AND FIRST_NAME <> 'Muzaffer';

See you in my next post.