Skip to main content

SQL JOINS


What is a Join?
The SELECT queries shown thus far in this training guide retrieve information from a single table at a time.
However, not all questions can be answered using just one table. When it's necessary to draw on
information that is stored in multiple tables, use a join condition—an operation that produces a result by
combining (joining) information in one table with information in another.
The contents of two original tables are shown below. These tables can be joined together using a SELECT
query which retrieves data from more than one table at a time.

table1 table2
+----+----+
| i1 | c1 |
+----+----+
| 1 | a |
| 2 | b |
| 3 | c |
+----+----+
3 rows in set (#.## sec)

+----+----+
| i2 | c2 |
+----+----+
| 2 | c |
| 3 | b |
| 4 | a |
+----+----+
3 rows in set (#.## sec)

mysql> SELECT * FROM table1 JOIN table2;
+----+----+----+----+
| i1 | c1 | i2 | c2 |
+----+----+----+----+
| 1 | a | 2 | c |
| 2 | b | 2 | c |
| 3 | c | 2 | c |
| 1 | a | 3 | b |
| 2 | b | 3 | b |
| 3 | c | 3 | b |
| 1 | a | 4 | a |
| 2 | b | 4 | a |
| 3 | c | 4 | a |
+----+----+----+----+
9 rows in set (#.## sec)

The above example returns a list of all data in both tables combined. This is known as a cross join. This
example employs the use of the JOIN keyword. However, there are multiple methods available for joining
tables which will be discussed within this chapter.
A 'Cross Join' is so called because each row of each table is crossed with each row in every other table to
produce all possible combinations. The result is known as a 'Cartesian Product'. A Cartesian product is a
mathematical (set theory) concept which basically means combining all elements from the first entity with
all elements of the second entity.
Joining tables this way has the potential to produce a very large number of rows because the possible row
count is the product of the number of rows in each table. A cross join between three tables contain 100,
200 and 300 rows, respectively, could return 100X200X300 = 6 million rows. That's a lot of rows, even
though the individual tables are small. In cases like this, normally a WHERE clause is useful for reducing
the result to a more manageable size. A join condition is critical to produce meaningful table joins.

Comments

Popular posts from this blog

PHP INTRODUCTION

                     PHP  (recursive acronym for  PHP: Hypertext Preprocessor ) is a widely-used open source general-purpose scripting language that is especially suited for web development and can be embedded into HTML. PHP stands for  P HP:  H ypertext  P reprocessor PHP is a server-side scripting language, like ASP PHP scripts are executed on the server PHP supports many databases (MySQL, Informix, Oracle, Sybase, Solid, PostgreSQL, Generic ODBC, etc.) PHP is an open source software PHP is free to download and use Why PHP? PHP runs on different platforms (Windows, Linux, Unix, etc.) PHP is compatible with almost all servers used today (Apache, IIS, etc.) PHP is FREE to download from the official PHP resource:  www.php.net PHP is easy to learn and runs efficiently on the server side What can PHP do? Anything. PHP is mainly focused on server-side scripting, so you can...

SQL Joins

1. Login in to the mysql client using the login name and password provided by your instructor (if not already logged in). 2. View the existing databases that the MySQL Server is maintaining. SHOW DATABASES; 3. Utilize the photo_album database. USE photo_album; ---- If Need be, recreate the database and load the SQL data in that will be used for this lab CREATE DATABASE photo_album; SOURCE /tmp/photo_album.sql; 4. Query the database to display the active primary customer (email, first and last name) and all the associated active login names associated with that customer. SELECT customers.email_customer, customers.fname, customers.lname, logins.login_name  FROM customers, logins WHERE customers.email_customer = logins.email_customer  AND customers.active_customer = 'True'  AND logins.active_login = 'True';

Storage Engine Breakdown

The following diagram represents a simplified view of the MySQL server and its interaction with the  storage engines. The following properties are storage engine dependant: • Storage Medium – Each table uses its own method of storing the data it contains. • Transactional Capabilities – Certain storage engines handle transactional processing which ensures that integrity of a database is maintained during the processing of multiple SQL statements.