Question

The following SQL statement uses a(n) _____. SELECT P_CODE, P_DESCRIPT, P_PRICE, V_NAME FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE = VENDOR.V_CODE; a. set operator b. natural join c. “old-style” join d. procedural statement

Answer 1

Answer:

The answer is c. “old-style” join.

Explanation:

SELECT P_CODE, P_DESCRIPT, P_PRICE, V_NAME

FROM PRODUCT, VENDOR

WHERE PRODUCT.V_CODE = VENDOR.V_CODE;

The SELECT clause represents all the columns to be displayed.

The FROM clause represents the tables which are used to extract the columns.

The WHERE clause shows the common column that exists in both the tables. This represents the old-style join when JOIN keyword was not used.

The tables are joined in two ways.

1. Using JOIN keyword

The syntax for this is given as

SELECT column1, column2

FROM table1 JOIN table2

ON table1.column3 = table2.column3;

This returns all rows from two tables having the same value of common column.

Two tables are taken at a time when JOIN keyword is used.

If more tables are to be used, they are mentioned as follows.

SELECT column1, column2

FROM table1 JOIN table2

ON table1.column3 = table2.column3

JOIN table3

ON table3.column4 = table1.column4

( ON table3.column5 = table2.column5 )  

The part in italics shows that the third table can either share the same column with table1 or table2.

The given query can be re-written using JOIN as shown.

SELECT P_CODE, P_DESCRIPT, P_PRICE, V_NAME

FROM PRODUCT JOIN VENDOR

ON PRODUCT.V_CODE = VENDOR.V_CODE;

2. Equating common column in WHERE clause

SELECT column1, column2

FROM table1, table2

WHERE table1.column3 = table2.column3;

This returns all rows from two tables having the same value of the common column.

Here, no keyword is used except the general syntax of the query. ON keyword as shown in the above example is replaced with WHERE.

Tables to be used are mentioned in FROM clause separated by commas.

Tables are joined based on the same column having same values.

Multiple tables can be joined using this style as follows.

SELECT column1, column2

FROM table1, table2, table3, table4

WHERE table1.column3 = table2.column3

AND table3.column5=table1.column5

AND table3.column4 = table2.column4;