Skip to main content

Section 1.4 Sets in Relational Database

Databases consist of sets of data. Tables are sets of structured data. Each "row" contains entries for one or more fields; each field is a specific type of data such as a string, integer, or date. One of the uses of databases is to connect tables based on shared fields. For an example see Appendix A.

The most common method for accessing databases, especially large, enterprise databases, is Structured Query Language (SQL pronounced sequel). Below is a very simple illustration of the format of SQL query statements.

Table 1.4.1. SQL query example

SELECT [ list of fields ]

each field from each table desired

FROM [ 1st table ]

(INNER/LEFT/RIGHT/OUTER) JOIN [ 2nd table ]

how to match rows of tables

WHERE <condition>

limits data used

A specific example that shows the midterm grade and placement test score of all students on the roll is below.

SELECT Midterm.Section, Compare.[Student ID], 
   Compare.[Placement Score 1], Compare.[Placement Score 2], 
   Midterm.Score, Midterm.Letter
FROM Compare 
INNER JOIN Midterm ON Compare.[Student ID] = Midterm.ID;

Note that the JOIN statements determine what set operation to perform on the tables. For example a left join takes all rows from the 1st table regardless of whether there is a matching row in the second table. An inner join requires that there be a matching row in both tables. An outer join uses all rows from both tables regardless of whether there is a match.

Example 1.4.2. SQL Left Join in Set Notation. 
SELECT [fields]
  FROM Table A LEFT JOIN Table B
  ON A.Key=B.Key

Set Notation: \(A\)

Example 1.4.3. SQL Inner Join in Set Notation. 
SELECT [fields]
  FROM Table A INNER JOIN Table B
  ON A.Key=B.Key

Set Notation: \(A \cap B\)

Checkpoint 1.4.4. 
SELECT [fields]
  FROM Table A RIGHT JOIN Table B
  ON A.Key=B.Key

Set Notation:

Checkpoint 1.4.5. 
SELECT [fields]
  FROM Table A LEFT JOIN Table B
  ON A.Key=B.Key
  WHERE B.Key IS NULL

Set Notation:

Checkpoint 1.4.6. 
SELECT [fields]
  FROM Table A RIGHT JOIN Table B
  ON A.Key=B.Key
  WHERE A.Key IS NULL

Set Notation:

Checkpoint 1.4.7. 
SELECT [fields]
  FROM Table A FULL OUTER JOIN Table B
  ON A.Key=B.Key

Set Notation:

Checkpoint 1.4.8. 
SELECT [fields]
  FROM Table A FULL OUTER JOIN Table B
  ON A.Key=B.Key
  WHERE A.Key IS NULL or B.Key IS NULL

Set Notation: