Select Statements

Part:   1  2  3   4 

(Continued from previous part...)

JointTable.sql - Example of Join Tables

To validate the join table logics mentioned in the previous section, I wrote the following SQL code, JointTable.sql:

-- JoinTable.sql
-- Copyright (c) 1999 by Dr. Herong Yang
--
-- Creating user table
DROP TABLE IF EXISTS User;
CREATE TABLE User (ID INT, Login CHAR(8), Dept_ID INT);
INSERT INTO User VALUES (1,'herong',1);
INSERT INTO User VALUES (2,'mike',2);
INSERT INTO User VALUES (3,'bill',3);
INSERT INTO User VALUES (4,'mary',3);
INSERT INTO User VALUES (5,'lisa',5);
-- Creating dept table
DROP TABLE IF EXISTS Dept;
CREATE TABLE IF NOT EXISTS Dept (ID INT, Name CHAR(16));
INSERT INTO Dept VALUES (1,'Math');
INSERT INTO Dept VALUES (3,'Chem');
INSERT INTO Dept VALUES (4,'Law');
INSERT INTO Dept VALUES (5,'English');
INSERT INTO Dept VALUES (5,'Latin');
-- Generating join tables
SELECT 'Running cross join' AS '---';
SELECT * FROM User CROSS JOIN Dept;
SELECT 'Running inner join' AS '---';
SELECT * FROM User INNER JOIN Dept ON User.Dept_ID=Dept.ID;
SELECT 'Running left outer join' AS '---';
SELECT * FROM User LEFT OUTER JOIN Dept ON User.Dept_ID=Dept.ID;
SELECT 'Running right outer join' AS '---';
SELECT * FROM User RIGHT OUTER JOIN Dept ON User.Dept_ID=Dept.ID;

Note that:

• "*" can be used as selection expression. It will be evaluated to a list of values from all columns in the select table.
• A select expression will have system default column name in the output table. You can change the default column name by using "AS new_name" option.
• When referring to a column in a table, you can use the fully quantified column name: table_name.column_name.

Output of JoinTabl.sql:

---
Running cross join
ID      Login   Dept_ID ID      Name
1       herong  1       1       Math
2       mike    2       1       Math
3       bill    3       1       Math
4       mary    3       1       Math
5       lisa    5       1       Math
1       herong  1       3       Chem
2       mike    2       3       Chem
3       bill    3       3       Chem
4       mary    3       3       Chem
5       lisa    5       3       Chem
1       herong  1       4       Law
2       mike    2       4       Law
3       bill    3       4       Law
4       mary    3       4       Law
5       lisa    5       4       Law
1       herong  1       5       English
2       mike    2       5       English
3       bill    3       5       English
4       mary    3       5       English
5       lisa    5       5       English
1       herong  1       5       Latin
2       mike    2       5       Latin
3       bill    3       5       Latin
4       mary    3       5       Latin
5       lisa    5       5       Latin
---
Running inner join
ID      Login   Dept_ID ID      Name
1       herong  1       1       Math
3       bill    3       3       Chem
4       mary    3       3       Chem
5       lisa    5       5       English
5       lisa    5       5       Latin
---
Running left outer join
ID      Login   Dept_ID ID      Name
1       herong  1       1       Math
2       mike    2       NULL    NULL
3       bill    3       3       Chem
4       mary    3       3       Chem
5       lisa    5       5       English
5       lisa    5       5       Latin
---
Running right outer join
ID      Login   Dept_ID ID      Name
1       herong  1       1       Math
3       bill    3       3       Chem
4       mary    3       3       Chem
NULL    NULL    NULL    4       Law
5       lisa    5       5       English
5       lisa    5       5       Latin

Notes on the output:

• Surprisingly, the cross join was performed with the outer loop on the right table columns. This is different than my expectation.
• Inner join, left outer join, and right outer join were performed as expected.

WHERE Clause

As I mentioned earlier, the WHERE clause modifies the base table by filtering out rows of data that do not satisfy the specified conditions. Here is its syntax:

WHERE where_condition

where "where_condition" is a predicate operation that will result a true or false condition.

WHERE clause samples:

WHERE Salary <= 45000.00
WHERE Salary > 45000.00 AND Salary <= 65000.00
WHERE Dept IN ('Math','Chem')
WHERE (Dept = 'Math' OR Dept = 'Chem') AND Salary <= 45000.00
WHERE User.Dept_ID = Dept.ID

(Continued on next part...)

Part:   1  2  3   4