Using Advance Query Techniques ColdFusion Help

This chapter delves into some more advanced techniques for connecting your Web site to a database optimizing your code troubleshooting your errors and improving your performance and speed. This is probably one of the more important chapters of the book. Once these techniques are mastered you will have progressed from a beginner ColdFusion developer into the Intermediate to Advanced realm of development

Working with Advanced SQL

Much of ColdFusion’s robustness comes with its ability to connect to databases so learning SQL is imperative if you want to be a great ColdFusion developer. In Chapter 10, you learned the basic SQL needed to retrieve and manipulate data in a database. This section explores some more complex SQL issues. If you want to learn even more check out the SQL book recommendations in the introduction of this book.

Joining

In a relational database tables are related to each other through primary and foreign keys. The primary key field helps to uniquely identify records in a single table. The foreign key field resides in a separate table and contains data that matches the values in a primary key field. The primary and foreign keys are used to match the records between the two tables.

Sooner or later (more likely sooner) you will find the need to retrieve related data from two or more tables. To do this, you will need to join the tables together on the primary. and foreign key fields. There are various method fur joining tables in your SQL statements. Some of these methods depend on your style of coding while others depend on which version of SQL your database is compatible with. Depending upon the version of SQL you are working with joining can be done in either the WHERE or the FROM clauses of your SQL statement. Both methods will be demonstrated in this chapter.

In order to learn how a join retrieves records from a database you need to become familiar with the various types of SQL joins (Table 11.1

TAB L E 11. 1:

TAB L E 11. 1:

Inner Joins

You may come across various terms used to refer to Inner Joins. They are most commonly called: Inner Joins Equi-Ioins and Natural Joins. While these three types of Inner Joins are very similar it is important to fully explain them so that there is not any confusion.

Inner join

Inner Joins are used to return all records where the data in the joined fields match. There are two ways that you can code an Inner Join SQL statement. In the first example the join will be performed in the FROM clause. This form of joining fulfills the SQL-92 ANSI standard and is recommended for compatibility with most versions of SQL. Performing the join in the FROM clause forces your database to join the tables before evaluating any WHERE clause conditions. This can make your query performance more efficient because it limits the amount of records that the WHERE conditions will be performed on

To create an Inner Join in the FROM clause specify the name of the first table then the type of join (in this case an Inner Join) followed by the name of the second table. Then enter the keyword ON followed by the primary key of the first table equal to the related
foreign key in the second table. This is the syntax: FROM tab 1el INNER JOIN tab 1e2 ON table l. pri mary_key = tab 1e2. forei gn_key. In the following example the join is indicated in bold type:

SELECT speakers.speaker_first_name,
speakers.speaker_last_name,
speaker_title. speaker_title
FROM speakers INNER JOIN speaker_title
ON speakers.speaker_id – speaker_title.speaker_id
WHERE (speakers.speaker_last_name LIKE a%)
ORDER BYspeakers.speaker_last_name,
speaker_title.speaker_title

The results can be seen in Figure 11.1

To create the same results using a join in the WHERE clause, make the two joined fields equal to each other. Transact SQL (sQL Server) and PL/SQL (Oracle) support this sort of join. The following query produces the exact same results as the previous query:

SELECT speakers.speaker_first_name,
.speakers.speaker_last_name,
speaker_title. speaker_title
FROM speakers, speaker_title
WHERE speakers.speaker_id – speaker_title.speaker_id
AND Cspeakers.speaker_last_name LIKE ‘a%’)
ORDER BY speakers.speaker_last_name,
speaker_citle.speaker_title
Results are also shown in Figure 11.1

FIGURE 11.1

FIGURE 11.1

Equi-Join

The second form of an Inner Join is called an Equi-Ioin An Equi-Ioin can be described exactly the same way as an Inner Join except that both joined fields are also included inthe SQL SELECT statement

When you output the results for an Equi-join, vou will get du licate data for both joined fields (Figure 11.2). An Equi-join waul ~look 11k is (ur.less at course you perform the join in the WHERE clause

SELECT speakers.speaker_id.
speakers.s~aker_first_name.
speakers.speaker_last_name,
speaker_title.speaker_id.
speaker_title.speaker_title
FROM speakers INNER JOIN speaker_title
ON speakers.speaker_id – speaker_title.speaker_id
WHERE (speakers.speaker_last_name LIKE a~)
ORDER BY speakers.speaker_last_name.
speaker_title. speaker_title

FIGURE 11.2

FIGURE 11.2

Natural Join

The only difference between the Equi-Ioin and the Natural Join is that only one of the joined fields is included in the SQL SELECT statement. This prevents duplication of data in the output of the results (see Figure 11.3). It does not matter which joined field you select. They will both contain the exact same data. The following example shows an example of a Natural Join

SELECT speakers.speaker_id,
speakers.speaker_first_name,
speakers.speaker_last_name,
speaker_title. speaker_title
FROM speakers INNER JOIN speaker_title
ON speakers.speaker_id – speaker_title.speaker_id
WHERE (speakers.speaker_last_name LIKE a%’)
ORDER BY speakers. speaker_l ast_name ,
speaker_title. speaker_title

FIGURE 11.3

FIGURE 11.3

Outer Joins

The three types of outer joins available to you are Right Outer Join. Left Outer Join and Full Outer Join. There are also three ways to code Outer Joins depending upon the version of SQL available to you: SQL-92 ANSI Standard Transact SQL (T-SQL), and PL/ SQL for Oracle

Left Outer Join

A Left Join is the most common Outer Join. It returns all records from the left table and only those records in the right table where the data in the joined fields match. Any records from the left table that have no matching records in the right table are preserved and will display NULL values in the fields from the right table. The syntax is similar to that used with an Inner Join, except you would use the LEFT OUTER JOIN keywords instead:

FROM tablel LEFT OUTER JOIN table2 ON tablel.primary_keytabl e2. forei gn_key. You can also use LEFT JOIN (and remove the OUTER keyword).

The following query would retrieve all records from speakers and only those records from the speaker_books table where the speaker _id’matches the speaker _id in the speakers table. So, even if a speaker did not have any books tied to their record, their name would still be returned and NULL values would be entered in the boak_ti,tl e and book_pri ce fields. This query uses the SQL-92 ANSI Standard and i” written as follows:

SELECT speakers.speaker_id.
speakers.speaker_first_name.
speakers.speaker_last_nam~.
speaker_books.book_title.
speaker_books.book_price
FROM speakers LEFT JOIN speaker_books
ON speak~rs.speaker_id – speaker_books.speaker_1d

The results are shown in Figure 11.4.

FIGURE 11.4

FIGURE 11.4

next query demonstrates using Transact SQL to create a Left Outer Join. The results would. be the same as those shown in figure ‘11.4and the code would be as follows:

SELECT speakers.speaker_id,
speakers.speaker_fjrst_name,
.speakers.speaker~last_name,
speaker_books.book_title,
speaker_books. qook_price
FROM speakers, speaker_books
WHERE speak.rs.speaker_id·· .speaker_books.speaker_id .

The final query demonstrates using Oracle (PL/SQL)to create a Left Outer Join. Notice that the Left Outer Join symbol (+) is placed on the opposite side of the Transact SQL Left Outer Join. The results would be the same as those shown in Figure 11.4 and are written like this

SELECT speakers.speaker_id,
speakers.speaker_first_name,
speakers.speaker_last_name,·
speaker_books.book_title
speaker_books.book_price
FR~M speakers, speaker_books
WHERE speakers.speaker_idspeaker_
books.speaker_id(+)

Right Outer Join

Right Outer Joins are the reverse of Left Outer Joins. They return all records from the right table and only those records in the left table where the data in the joined fields match. Any records from the right table that have no matching records in the left table will be preserved and display NULL values in the fields from the left table. The syntax is similar to that used with a Left Outer Join except you would use the Right Outer Join keywords instead: FROM tablel RIGHT OUTER JOIN table2 ON tablel.primary_key = tab 1e2. fore; gn_key. You can also use Right Join (and remove the Outer keyword).

The following query would retrieve all records from speakers_books and only those records from the speakers table where the speaker _id matches the speaker _id in the speake rs_books table. So, even if a book were not tied to any speaker, the record would be returned and NULL values would be entered in the speaker _id,speaker _fi rst_name, and speaker _, ast_name fields. The followirig query uses the SQL-92 ANSI Standard:

SELECT speakers.speaker_,d,
speakers.speaker_first_name,
speakers speaker_last_na~e,
~peaker_books.book_title,
speaker_books.book_price
FROM speakers RIGHT JOIN speaker_books
ON speakers.speaker_id – speaker_bOoks.speaker_id

The results are shown in Figure 11.5.

The next query demonstrates using Transact SQL to create a Right Outer Join. The results would be the same as those shown in Figure 11.5 and are written as follows

~ELECT speakers.speaker_id,
speakers.speaker_first_name,
speakers. speaker_last_name ,
speaker_bcoks.book_title,
sot’aker_books.book_price
FROM speakers, speaker_books
WHERE speakers.speaker_id -* speaker_books.~p.aker_id

FIGURE 11.5

FIGURE 11.5

The final query demonstrates using Oracle (PL/SQL) to create a Right Outer Join. Notice that the Right Outer Join symbol (+) is placed on the opposite side of the Transact _SQLRight Outer Join. The results would be the same as those shown in Figure 11.5 and would look like this

SELECT .speakers.speaker_id,
speakers.speaker_first_name,
speakers,speaker_last_n~~e,
speaker_books.book_title,
speaker_books. book_price
FROM speakers, speaker_books
WHERE speakers.speaker_id(+)·
speaker_books.speaker_id

Basically you can get the same results from a Left Outer Join and a Right Outer Join by just flipping the tables around. For example the following two FROM clauses produce the same results:

FROM speakers LEFT JOIN speaker_books
ON speakers.speaker_id – speaker_books.speaker_id
FROM speaker_books RIGHT JOIN speakers
ON speaker_books.speaker_id – speakers.speaker_id

Full Outer Join

A Full Outer Join is not fully supported by all versions of SQL. It combines the results of a Left and Right Outer Join. If your version of SQL does “not support Full Outer Joins then you can Union two queries together; one query would contain the Left Outer Join while the other would contain a Right Outer Join

.SELECT speakers.speaker_first_name,
speakers.speaker_last_name ,
speaker_title.speaker_title
FROM speaKers FULL OUTER JOIN speaker_title
ON speakers.speaker_id • speaker_title.speaker_id
ORDER BYspeakers.speaker_last_name,
speaker_title.speaker_title

Posted on November 16, 2015 in Using Advance Query Techniques

Share the Story

Back to Top
Share This