Entry
Database: SQL: Query: Key: What is a query to join 2 database tables via its keys? [foreign/primary]
Aug 22nd, 2008 09:31
Knud van Eeden,
----------------------------------------------------------------------
--- Knud van Eeden --- 01 January 2004 - 00:45 am --------------------
Database: SQL: Query: Key: What is a query to join 2 database tables
via its keys? [foreign/primary]
---
In relational database management, to JOIN means to match one table
against another, based on some condition, creating a third table with
data from the matching tables.
===
Usually a join tests for the equality of values in column1 of table1
against values in column2 of table2.
===
-Method: Test for equality of values of columns using equal '='
statement
===
Use e.g. a
SQL SELECT testing for equal values.
===
This is equivalent of one of the SQL JOIN statements.
---
e.g.
--- cut here: begin --------------------------------------------------
SELECT
E.FirstName
FROM
Employees E,
Managers M
WHERE
E.FirstName = M.FirstName;
--- cut here: end ----------------------------------------------------
===
Running this SQL query with this data:
Employee table
First Name
------------
Johnny
Bill
Knud
Vanessa
Manager table
First Name
------------
Bill
Knud
---
will show
Bill
Knud
===
Using Microsoft SQL server
(using database client 'isql.exe' -U yourusername -P yourpassword -i
yourSQLcommandFilename)
--- cut here: begin --------------------------------------------------
USE database1;
DROP TABLE table1;
DROP TABLE table2;
CREATE TABLE table1 (
FirstName VARCHAR( 20 )
)
;
CREATE TABLE table2 (
FirstName VARCHAR( 20 )
)
;
INSERT INTO table1
( FirstName )
VALUES
( 'Johnny' )
;
INSERT INTO table1
( FirstName )
VALUES
( 'Bill' )
;
INSERT INTO table1
( FirstName )
VALUES
( 'Knud' )
;
INSERT INTO table1
( FirstName )
VALUES
( 'Vanessa' )
;
INSERT INTO table2
( FirstName )
VALUES
( 'Bill' )
;
INSERT INTO table2
( FirstName )
VALUES
( 'Knud' )
;
SELECT
table1.FirstName
FROM
table1,
table2
WHERE
table1.FirstName = table2.FirstName;
--- cut here: end ----------------------------------------------------
That will show a screen output similar to the following:
--- cut here: begin --------------------------------------------------
FirstName
--------------------
Bill
Knud
--- cut here: end ----------------------------------------------------
===
Using MySql
(using database client 'mysql.exe --user yourusername')
--- cut here: begin --------------------------------------------------
USE database1;
DROP TABLE IF EXISTS table1;
DROP TABLE IF EXISTS table2;
CREATE TABLE table1 (
FirstName VARCHAR( 20 )
)
;
CREATE TABLE table2 (
FirstName VARCHAR( 20 )
)
;
INSERT INTO table1
( FirstName )
VALUES
( 'Johnny' )
;
INSERT INTO table1
( FirstName )
VALUES
( 'Bill' )
;
INSERT INTO table1
( FirstName )
VALUES
( 'Knud' )
;
INSERT INTO table1
( FirstName )
VALUES
( 'Vanessa' )
;
INSERT INTO table2
( FirstName )
VALUES
( 'Bill' )
;
INSERT INTO table2
( FirstName )
VALUES
( 'Knud' )
;
SELECT
table1.FirstName
FROM
table1,
table2
WHERE
table1.FirstName = table2.FirstName;
--- cut here: end ----------------------------------------------------
That will show a screen output similar to the following:
--- cut here: begin --------------------------------------------------
+-----------+
| FirstName |
+-----------+
| Bill |
| Knud |
+-----------+
2 rows in set (0.00 sec)
--- cut here: end ----------------------------------------------------
===
Using Oracle
(using database client sqlplus.exe /nolog)
--- cut here: begin --------------------------------------------------
CONNECT SYSDBA/k@orclknud AS SYSDBA;
-- DROP TABLE table1;
-- DROP TABLE table2;
CREATE TABLE table1 (
FirstName VARCHAR2( 20 )
)
;
CREATE TABLE table2 (
FirstName VARCHAR2( 20 )
)
;
INSERT INTO table1
( FirstName )
VALUES
( 'Johnny' )
;
INSERT INTO table1
( FirstName )
VALUES
( 'Bill' )
;
INSERT INTO table1
( FirstName )
VALUES
( 'Knud' )
;
INSERT INTO table1
( FirstName )
VALUES
( 'Vanessa' )
;
INSERT INTO table2
( FirstName )
VALUES
( 'Bill' )
;
INSERT INTO table2
( FirstName )
VALUES
( 'Knud' )
;
SELECT
table1.FirstName
FROM
table1,
table2
WHERE
table1.FirstName = table2.FirstName;
--- cut here: end ----------------------------------------------------
That will show a screen output similar to the following:
--- cut here: begin --------------------------------------------------
FIRSTNAME
--------------------
Bill
Knud
--- cut here: end ----------------------------------------------------
===
More general
SELECT
*
FROM
<your table1>,
<your table2>
WHERE
<your table name1>.<your column name1> = <your table name2>.<your
column name2>;
===
-Method: Using nested SELECT statement and SQL 'IN'
The SQL query is equivalent to the usual simpler SQL query
--- cut here: begin --------------------------------------------------
SELECT * FROM table1 WHERE table1.FirstName IN ( SELECT
table2.FirstName FROM table2 );
--- cut here: end ----------------------------------------------------
More general
--- cut here: begin --------------------------------------------------
SELECT * FROM <your table1> WHERE <your table name1>.<your column
name1> IN ( SELECT <your table name2>.<your column name2> FROM
table2 );
--- cut here: end ----------------------------------------------------
===
-Method: Using SQL INNER JOIN statement
The SQL query is equivalent to the following SQL query
--- cut here: begin --------------------------------------------------
SELECT
table1.FirstName
FROM
table1
INNER JOIN
table2
ON
table1.FirstName = table2.FirstName
;
--- cut here: end ----------------------------------------------------
More general
--- cut here: begin --------------------------------------------------
SELECT
*
FROM
table1
INNER JOIN
table2
ON
<your table name1>.<your column name1> = <your table name2>.<your
column name2>
;
--- cut here: end ----------------------------------------------------
===
Internet: see also:
---
Database: Language: SQL: Overview: Can you give an overview of links
about SQL?
http://www.faqts.com/knowledge_base/view.phtml/aid/32811/fid/54
----------------------------------------------------------------------
