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

By Means Of A Cross Join

The Cross Join is also identified as Cartesian Product, among two (2) tables or relations links each and every row or tuple from one table or relation with each and every row or tuple of the table or relation. The total number of rows or tuples in the outcome group is the total number of rows or tuples in the first table or relation multiplied with the total number of rows or tuples in the second table or relation. This infers that if Table 1 has seven (7) rows or tuples plus Table 2 has eight (8) rows or tuples, then the entire seven (7) rows or tuples of Table 1 are linked with every eight (8) rows or tuples of Table 2. Consequently, the outcome group will have a total of fifty six (56) rows or tuples. The code for implementing the Cross Join is as follows:

SELECT My_Column_Name , My_Column_Name , [ My_Column_Name ] FROM My_Table1_Name CROSS JOIN My_Table2_Name

here,

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

· My_Table1_Name and My_Table2_Name – These are the title of the tables or relations which are to be linked together to obtain the required information.

· CROSS JOIN – It is the one of the joining type.

By Means Of An Equi Join

The Equi Join is the identical as the Inner Join plus it link tables or relations by means of the Foreign Key. Nevertheless, the Equi Join is castoff to show each and every column or attribute from both the tables / relations. The shared column or attribute from every linking tables or relations is shown. The code for implementing the Equi Join is as follows:

SELECT * FROM My_Table1_Name JOIN My_Table2_Name ON My_Table1_Name . My_Ref_Column_Name My_Join_Operator My_Table2_Name . My_Ref_Column_Name

here,

· My_Table1_Name and My_Table2_Name – These are the title of the tables or relations which are to be linked together to obtain the required information.

· JOIN – It is the one of the joining type, over here it is working like the INNER join.

· My_Join_Operator – It is the judgment operator grounded on which the linking functions will be implemented.

· My_Table1_Name . My_Ref_Column_Name and My_Table2_Name . My_Ref_Column_Name – These are the title of the columns or attributes depending on which the linking function is implemented.

There is dissimilarity among the Equi Join and the Inner Join, which is that the Equi Join is castoff to recover every column or attribute from both the tables / relations, whereas the Inner Join is castoff to recover only a number of columns or attributes from the tables or relations.

By Means Of A Self – Join

In a Self – Join, a table or relation is linked with itself. As a consequence, one (1) row or tuple in a table or relation associates with another row or tuple in the similar table or relation. In a Self – Join, a table or relation title is castoff two times in the enquiry. For that reason, to distinguish the two (2) occurrences of a sole table or relation, the table or relation is provided two (2) codename (alias) labels. The code for implementing the Self – Join is as follows:

SELECT My_Column_Name , My_Column_Name , [ My_Column_Name ] FROM My_Table_Name_A , My_Table_Name_B WHERE My_Table_Name_A . My_Ref_Column_Name My_Join_Operator My_Table_Name_B . My_Ref_Column_Name

here,

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

· My_Table_Name_A and My_Table_Name_B – These are the title of the tables or relations which are to be linked together to obtain the required information, in this case both the table or relation title is identical only the codename (alias) is exclusive.

· WHERE – It is the one of the condition that has to be matched before any Self – Join can occur.

· My_Join_Operator – It is the judgment operator grounded on which the linking functions will be implemented.

· My_Table_Name_A . My_Ref_Column_Name and My_Table_Name_B . My_Ref_Column_Name – These are the title of the columns or attributes depending on which the linking function is implemented.

Inquiring Information By Means Of Sub – Queries

At the time of enquiring information from several tables or relations, an individual may want to make usage of the outcome of one (1) enquiry as an response for the condition of additional enquiry. For an instance, in the XYZBank database, an individual want to sight the details of the customer who have taken loan and their respective EMI is pending. In such circumstances, an individual can make use of sub – queries to allot data to the expression of another enquiry.

Any sub – query is the SQL command which is castoff inside a different SQL command. The sub – queries are placed inside the WHERE clause otherwise inside the HAVING clause of the SELECT, INSERT, UPDATE and DELETE commands. The enquiry which signifies the base query is known as an External Query, plus the query which signifies the sub – query part is known as an Internal Query. The database engine run the Internal Query before then gives back the outcome to the External Query for computing the outcome set.

Subjected to the result that has been produced by means of the sub – query as well as the tenacity for which it is to be castoff in the External Query, an individual can practice dissimilar keywords, functions as well as operators in sub – queries.

In the upcoming part we will be discussing the By Means Of The IN and EXISTS Keywords, By Means Of IN keyword, and By Means Of EXISTS Keyword.