17. DBMS

    1. What is an SQL INNER JOIN?

The INNER JOIN keyword return rows when there is at least one match in both tables. If there are rows in "Persons" that do not have matches in "Orders", those rows will NOT be listed.

SELECTcolumn_name(s)
FROMtable_name1
INNER JOINtable_name2
ONtable_name1.column_name=table_name2.column_name

OUTER JOIN lets you see both matched rows and unmatched ones,

It's great for finding out:

• which, if any, rows in one table do not have a matching related row in another table.

• It also helps you find rows that have matches on a few rows but not on all.

• In addition, it's useful for creating input to a report where you want to show all categories (regardless of whether matching rows exist in other tables) or all customers (regardless of whether a customer has placed an order).

Following is a small sample of the kinds of requests you can solve with an OUTER JOIN.

Find Missing Values

Sometimes you just want to find what's missing. You do so by using an OUTER JOIN with a test for Null. Here are some "missing value" problems you can solve.

"What products have never been ordered?"
"Show me customers who have never ordered a helmet."
"List entertainers who have never been booked."
"Display agents who haven't booked an entertainer."
"Show me tournaments that haven't been played yet."
"List the faculty members not teaching a class."
"Display students who have never withdrawn from a class."
"Show me classes that have no students enrolled."
"List ingredients not used in any recipe yet."
"Display missing types of recipes."

Find Partially Matched Information

Particularly for reports, it's useful to be able to list all the rows from one or more tables along with any matching rows from related tables. Here's a sample of "partially matched" problems you can solve with an OUTER JOIN.

"List all products and the dates for any orders."
"Display all customers and any orders for bicycles."
"Show me all entertainment styles and the customers who prefer those styles."
"List all entertainers and any engagements they have booked."
"List all bowlers and any games they bowled over 160."
"Display all tournaments and any matches that have been played."
"Show me all subject categories and any classes for all subjects."
"List all students and the classes for which they are currently enrolled."
"Display all faculty and the classes they are scheduled to teach."
"List all recipe types, all recipes, and any ingredients involved."
"Show me all ingredients and any recipes they're used in."

A UNION JOIN is a FULL OUTER JOIN with the matching rows removed

As you might expect, not many commercial implementations support a UNION JOIN. Quite frankly, we're hard pressed to think of a good reason why you would want to do a UNION JOIN.

A JDBC driver

1. allows a Java application/client to communicate with a SQL database

2. is a Java class that implements the JDBC's java.sql.Driver interface.

3. Converts program SQL requests for a particular database.

Why canload a JDBC Driverby using Class.forName()