Inquiring Information By means of JOINs As Well As Sub – Queries Part – 3

By Means Of The IN and EXISTS Keywords

The sub – query gives back the data which is to be castoff by means of the External Query. A sub – query can give back one (1) or numerous (N) data. Subjected to the necessity, an individual can practice these data in diverse methods in the External Query.

For an instance, in the XYZBank database, an individual want to show the name of the customer who have a savings account with the account no. of ‘48494858548’. To do this job, an individual may practice the subsequent syntax:

SELECT Name FROM [ XYZBank ] . [ Customer ] . [ Details ] WHERE

CustomerID = ( SELECT CustomerID FROM [ XYZBank ] . [ Customer ] .

[ SavingsAc] WHERE AccountNo = ‘ 48494858548 ’ )

In the previous syntax, the internal sub – query gives back the CustomerID column or attribute of the customer who is having an AccountNo as 48494858548. By means of this CustomerID, the External Query gives back the Name of the customer from the [ Customer ] . [ Details ] table or relation.

In the previous instance, the sub – query gives back a sole value. But, from time to time, an individual may need to present more than one (1) data from the sub – query. Additionally, an individual may want to practice a sub – query simply to validate the presence of a number of records as well as grounded on which an individual want to run the External Query. Intended for this an individual can stipulate dissimilar types of settings on sub – queries by means of the subsequent keywords:

· IN

· EXISTS

By Means Of IN keyword

Certainly when a sub – query gives back more than one (1) data, an individual may want to run the External Query when the data inside the column or attribute stated in the condition matches any data from the outcome set of the sub – query. Intended for this job, an individual should practice the IN keyword. The code for using the IN keyword is as follows:

SELECT My_Column_Name , My_Column_Name [ , My_Column_Name ] FROM My_Table_Name WHERE My_Column_Name [ NOT ] IN ( SELECT My_Column_Name FROM My_Table_Name [ WHERE My_Condition ] )

here,

· My_Column_Name – It is the label of the column or attribute that an individual wants to select.

· My_Table_Name – It is the title of the table or relation from which the columns or attributes are to be selected.

· [ NOT ] IN – It is the keyword that will check whether any values from the outcome set of the sub – query matches the External Query, if matched then the External Query will be executed. The [ NOT ] keyword is optional and when used it acts in a contrasting way, that is when no values from the sub – query matches with the External Query then the External Query will be executed only.

For an instance, in the XYZBank database, an individual want to show the names of the customer who have taken ‘Personal’ loan. To do this job, an individual may practice the subsequent syntax:

SELECT Name FROM [ XYZBank ] . [ Customer ] . [ Details ] WHERE

CustomerID IN ( SELECT CustomerID FROM [ XYZBank ] . [ Customer ] . [ Loan] WHERE LoanType = ‘ Personal ’ )

In the previous syntax, the internal sub – query gives back the CustomerID column or attribute of the customers who have taken a Personal loan. By means of this CustomerID, the External Query gives back the Name of the customers from the [ Customer ] . [ Details ] table or relation.

By Means Of EXISTS Keyword

An individual may practice a sub – query to validate whether a group data is present or not too. Intended for this, an individual should practice the EXISTS keyword with a sub – query. The EXISTS keyword, at all times gives back a TRUE or FALSE data only. The EXISTS keyword validates the presence of rows or tuples agreeing to the circumstance stated in the Internal Query as well as inform the presence status to the External Query. The sub – query gives back a TRUE data when the outcome of the sub – query encloses any row or tuple.

The syntax presented with the EXISTS keyword varies from other syntax. The EXISTS keywords is not headed through any column or attribute name, constant or else additional expression, plus it have an asterisk (*) in the SELECT statement list of the Internal Query. The code of the EXISTS keyword in the SELECT statement is as follows:

SELECT My_Column_Name , My_Column_Name [ , My_Column_Name ] FROM My_Table_Name WHERE EXISTS ( SELECT * FROM My_Table_Name [ WHERE My_Condition ] )

here,

· My_Column_Name – It is the label of the column or attribute that an individual wants to select.

· My_Table_Name – It is the title of the table or relation from which the columns or attributes are to be selected.

· EXISTS – It is the keyword that will validate the presence of rows or tuples agreeing to the circumstance stated in the Internal Query as well as inform the presence status to the External Query. The EXISTS keyword, at all times gives back a TRUE or FALSE data only.

· External Query will be executed only.

For an instance, in the XYZBank database, an individual want to show the list consisting of the customer ID, customer names and phone no. of all the customers who have taken ‘Personal’ loan. To do this job, an individual may practice the subsequent syntax:

SELECT CustomerID , Name , Phone FROM [ XYZBank ] . [ Customer ] . [ Details ] WHERE EXISTS ( SELECT * FROM [ XYZBank ] . [ Customer ] . [ Loan] WHERE

CustomerID = Customer . Details . CustomerID AND LoanType = ‘ Personal ’ )

In the previous syntax, the internal sub – query validate whether the particular group data is present or not. If it returns TRUE then the External Query gives back the CustomerID , Name , Phone of the customers from the [ Customer ] . [ Details ] table or relation.

A sub – query should be bounded by the parentheses as well as it cannot practice the usage ORDER BY or else the COMPUTE BY clause.

In the upcoming part we will be going to discuss the By Means Of Altered Judgment Operators, By Means Of Summative Functions, By Means Of Nested Sub – Queries, and By Means Of Associated Sub – Queries.