• Login

DataSoft Corporation

Select Joins Multiple Tables Examples

Multi-database Join

When needed, a database name may be prepended to an aliased table name in the FROM clause, to distinguish among tables from two or more different databases that are used in a join.

All of the specified databases must be serviced by the same database engine and have the same database code page settings. The databases do not need to reside on the same physical volume. The current database may be secure or non-secure, but all other databases in the join must be non-secure. With regard to Referential Integrity, all RI keys must exist within the same database. (See also Encoding.)

Literal database names are not permitted in the select-list or in the WHERE clause. If you wish to refer to specific columns in the select-list or in the WHERE clause, you must use an alias for each specified table. See examples.

Assume two separate databases, “accounting” and “customers,” exist on the same server. You can join tables from the two databases using table aliases and SQL syntax similar to the following example:

SELECT ord.account, inf.account, ord.balance, inf.address

FROM accounting.orders ord, customers.info inf

WHERE ord.account = inf.account

============ 

In this example, the two separate databases are “acctdb” and “loandb.” The table aliases are “a” and “b,” respectively.

SELECT a.loan_number_a, b.account_no, a.current_bal, b.balance

FROM acctdb.ds500_acct_master b LEFT OUTER JOIN loandb.ml502_loan_master a ON (a.loan_number_a = b.loan_number)

WHERE a.current_bal <> (b.balance * -1)

ORDER BY a.loan_number_a

============ 

Left Outer Join

The following example shows how to access the “Person” and “Student” tables from the DEMODATA database to obtain the Last Name, First Initial of the First Name and GPA of students. With the LEFT OUTER JOIN, all rows in the “Person” table are fetched (the table to the left of LEFT OUTER JOIN). Since not all people have GPA’s, some of the columns have NULL values for the results. This is how outer join works, returning non-matching rows from either table.

SELECT Last_Name,Left(First_Name,1) AS First_Initial,Cumulative_GPA AS GPA FROM “Person”

LEFT OUTER JOIN “Student” ON Person.ID=Student.ID

ORDER BY Cumulative_GPA DESC, Last_Name

Assume that you want to know everyone with perfectly rounded GPA’s and have them all ordered by the length of their last name. Using the MOD statement and the LENGTH scalar function, you can achieve this by adding the following to the query:

WHERE MOD(Cumulative_GPA,1)=0 ORDER BY LENGTH(Last_Name)

 

Right Outer Join

The difference between LEFT and RIGHT OUTER JOIN is that all non matching rows show up for the table defined to the right of RIGHT OUTER JOIN. Change the query for LEFT OUTER JOIN to include a RIGHT OUTER JOIN instead. The difference is that the all non-matching rows from the right table, in this case “Student,” show up even if no GPA is present. However, since all rows in the “Student” table have GPA’s, all rows are fetched.

SELECT Last_Name,Left(First_Name,1) AS First_Initial,Cumulative_GPA AS GPA FROM “Person”

RIGHT OUTER JOIN “Student” ON Person.ID=Student.ID

ORDER BY Cumulative_GPA DESC, Last_Name

 

Cartesian Join

A Cartesian join is the matrix of all possible combinations of the rows from each of the tables. The number of rows in the Cartesian product equals the number of rows in the first table times the number of rows in the second table.

Assume you have the following tables in your database:

Table 46

Addr Table

EmpID

Street

E1

101 Mem Lane

E2

14 Young St.

Table 47

Loc Table

LocID

Name

L1

PlanetX

L2

PlanetY

The following performs a Cartesian JOIN on these tables:

SELECT * FROM Addr,Loc

This results in the following:

Table 48

SELECT Statement with Cartesian JOIN

EmpID

Street

LocID

Name

E1

101 Mem Lane

L1

PlanetX

E1

101 Mem Lane

L2

PlanetY

E2

14 Young St

L1

PlanetX

E2

14 Young St

L2

PlanetY

Friday, 19 April 2019 Posted in Example SQL Statements
 DataSoft Store  Knowledgebase  Contact Us
 Sitemap  Privacy Policy
    Customer Service


Copyright © 2019 DataSoft Corporation All rights reserved.