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 .
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);
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;
Used operators to merge multiple tables and queries . We have 4 set operators : MINUS , UNION , UNION ALL and INTERSECT .
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;
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;
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;
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 .
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);
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 .
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.
501 views last month, 1 views today