Skip to main content

Posts

Showing posts with the label join

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';

Outer Joins

Whereas an INNER JOIN will find combinations of matching rows from joined tables, the OUTER JOIN also finds the instances where a row in one table has no match in another table. An OUTER JOIN find matches (just like an INNER JOIN), but also identifies mismatches. Two forms of outer joins are LEFT JOIN and RIGHT JOIN . These do not use the comma separator or the INNER JOIN keywords. They each answer the same kinds of questions, but differ slightly in their syntax. That is a LEFT JOIN can always be re-written into a equivalent RIGHT JOIN. (In the following sections, the terms left table and right table refer to the tables named first and second in the FROM clause, respectively.)  Left Outer Join A left join is a type of outer join, written using the LEFT JOIN keywords. A left join treats the left table (the first one named) as a reference table and produces output for each row selected from it, whether or not the row is matched by rows in the right table. Like a join writt

Inner Joins

A join that identifies combinations of matching rows from two tables is called an inner join. The joining is done by connecting one table, using the primary key, another table that references it with a foreign key. Inner joins may be written using two different syntaxes. One syntax lists the tables to be joined separated by a comma. The other uses the INNER JOIN keywords.  Comma Separated Inner Join To combine data from two or more tables to find the detail of records referenced in a foreign key , a table join must be performed. The most common way to do this is by specifying a list (comma separated) of tables in the FROM clause of the SELECT and using the WHERE clause to indicate the relationship between the tables. A simple question that might be asked about the information in the world database is, “What are the country and corresponding capital cities (along with ID)?” To answer that question, the following two individual queries could be executed… mysql> SELECT Na