Getting Data From More Than One Tables

Hi ,

in this article I will tell you about the joins .

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 .

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 .

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 .

SINGLE LINE SUB QUOTES1

  • Returns a single row .

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

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 .

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 ” .

  • 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 ” .

See you in my next post.