CrQSS Join ColdFusion Help

A Cross Join usually occurs when a programmer has forgotten to include a join in their SQL statement. The total results from a Cross Join are calculated by multiplying the number of rows in the first table by the number of rows in the second table. This is also called a Cartesian product. Even on tables with minimal records, the result set can become quite large. On large tables it can overload your server and cause major. performance problems at the very least

In the following query you will notice that although the book’s title “Mastering Cold-Fusion 4.0” has no author associated with it. the results have matched up every speaker as an author of this book (see Figure 11.6): The following is obviously not correct:

SELECT speaker_books.book_title,
speakers.speaker_first_name,
speakers.speaker_last_name
FROM speakers,
speaker_books
ORDER BY speaker_books.book_title

FIGURE 11.6

FIGURE 11.6

One reason you may want to use a Cross Join is to create large amounts of test data. For example if you needed to test a ColdFusion loop for performance a Cross Join could provide large amounts of data for that purpose. If you do intend to use a Cross Join you can also use the ANSI standard (if supported by your database) as shown in the following query

SELECT speaker_books.book_title,
speakers.speaker_first_name,
speakers.speaker_last_name
FROM speakers CROSS JOIN speaker_tK~ks
ORDER BY speaker_books.book_title

Nesting Joins

Now what if you want to join more than two tables? You can! It is much easier to create multiple table joins using Studio’s Query Builder than to attempt it yourself. Nevertheless for the braveof heart multiple joins will be briefly demonstrated.

If you use the SQL-92 ANSI standard to join multiple tables you will need to nest your join statements in the FROM clause. First look at the following query

SELECT speakers.speaker_first_name.
speakers.speaker_last_name.
category_desc.category_name
FROM speakers LEFT JOIN (speaker_category LEFT JOIN category-desc ON
speaker_category.speaker_category_id category_desc.speaker_category_id ) ~
speakers.speaker id – speaker category. speaker id
ORDER BY speakers.speaker_last_name.
category_desc.category_name

If you look closely at the FROM clause you will see that three tables are involved. in the join.

The outer layer of the join (underlined) is creating a Left Join between the speakers table and the speaker_category results of the nested join. nus tells the database “give me all records from speakers and only those records from the nested join that match the records in speakers.”

The nested join (in bold) is creating a left join between speaker_category and category_desc. This tells the database “give me all records from speaker_category . and only those from category.:..desc that match.” Speaker_category is a table that holds a speaker _id matched up with a category_id. The actual names of the categories are stored in the category_desc table.

The results of this join can be seen in Figure 11.7

If you use Transact SQL, your query might look like this:

SELECT speakers.speaker_first_name.
speakers.speaker_last_name.
category_desc.category_name
FROM speakers.
speaker_category.
category_desc
WHERE speakers.speaker_1d *. speaker_category.speaker_1d
AND speaker_category.speaker_category_id *-
category_desc.speaker_category_id
ORDER BY speakers.speaker_last_name.
category_desc.category_name

FIGURE 11.7

FIGURE 11.7

Finally, if you use PL/SQL, your query could be like this:

speakers.speaker_last_name,
category_desc.category_name
FROM speakers,
speaker_category,
category_desc
WHERE speakers.speaker_id – speaker_category.speaker_id(+)
AND speaker_category.speaker_category_idcategory_
desc.speaker_category_id(+)
ORDER BY speakers.speaker_last_name,
category_desc.category_name

Subqueries

A sub query is a query nested inside of another query. It can be nested inside of a SELECT UPDATE INSERT or DELETE query in either the SELECT FROM or WHERE clause. If the sub query returns more than one result it can only be used in the WHERE clause.

Subqueries can be nested many levels deep. After about the second nesting subqueries become difficult to interpret and decipher. Usually at that point, it is better to accomplish your task with a join. Sub queries and joins are often two different approaches to doing the same thing. What can he done in a sub query can usually be done in a join as well. In fact joins are usually better when it comes to performance.

Subqueries are useful for returning a value to be compared, a list of values to be used with the IN operator, or a record set to be used with the EXISTS operator (discussed later in this chapter). If yop are comparing a subquery to a value, the following operators can be used: =, <, >, <=, >=, and <>.

There are several rules that must be followed when using subqueries

• The subquery must be surrounded by parentheses O.
• Subqueries cannot be used in the ORDER BYclause.
•Subqueries cannot include the following clauses: ORDER BYSELECT INTO COMPUTE GROUP BY,or HAVING (except in more advanced forms of SQL).
• The SELECT clause of a subquery cannot contain fields with the data type of text or image.

Additional rules for each type of subquery are explained in the following sections

Subqueries and the IN Operator

When comparing the results of a subquery with the IN operator the subquery can return many records but must return only one field that matches the data type of the value being compared. This subquery must be used in the WHERE clause of the parent query

The following query demonstrates the use of a subquery in the WHERE clause using the IN operator. The results are shown in Figure 11.8. The results will show all speaker names that have books selling at a price over $5.00:

SELECT speakers.speaker_first_name,
speakers.speaker_last_name
FROM speakers
WHERE speakers.speaker_id IN (
SELECT speaker_books.speaker_id
FROM speaker_books
WHERE speaker_books.book_price >
)

FIGURE 11.8

FIGURE 11.8

Subqueries and the = Operator

If the subquery is being compared to a single value using the equal (=) operator, it must return a single value. The single value must have the same data type as the value it is being compared to. This subquery must be used in the WHERE clause of the parent query.

The following query demonstrates the use of a subquery in the WHERE clause using the = operator. The results will show all the books in the database written by Guy Kawasaki. The subquery is used to gEc*thte speake r_; d of Guy.

SELECT speaker_books.book_title,
speaker_books.book_price
FROM speaker_books
WHERE speaker_books.speaker_id = (
SELECT speakers.speaker_id
FROM speakers
WHERE speaker_first_name ~ ‘Guy’
AND sp~aker_last_name – Kawasaki’
)

The results are shown in Figure 11.9

FIGURE 11.9

FIGURE 11.9

Subqueries and the EXISTS Operator

If the subquery is being used with the EXISTS operator, the subquery is evaluated for each row in the parent query. If the subquery has a result for that row, then it is returned. You can also use the NOToperator to only return rows in the parent query if zero rows are returned in the subqueiy. The subquery SELECT clause must use the * In the following query, a speaker is returned only if the speaker _id from the parent
query is the same as the speaker _; d in the subquery. Results are shown in Figure 11.10.
This is also known as a correlated subquery because it references fields in the parent
query:
SELECT speakers.speaker_first_name,
speakers.speaker_last_name
FROM speakers
WHERE EXISTS (
SELECT *
FROM speaker_books
WHEREspeaker_books.speaker_id – speakers.speaker_id
)wildcard.

FIGURE 11.10

FIGURE 11.10

 

Posted on November 16, 2015 in Using Advance Query Techniques

Share the Story

Back to Top
Share This