Specifying those values after the table name allows us to refer to those tables The second 'A' and second 'B'Īre aliases. See how the query above has 'TABLEA A' and 'TABLEB B'. INNER JOINs can be described by the Venn diagram's intersectionality which states that only the things that are in both of the sets are returned. Id | username | enabled | last_login | user_id | street | city | state Only 2 records are returned for the query the result does not contain the user Alice Munro who does not have an address. In the query below, the line INNER JOIN (addresses) ON (users.id = er_id) creates the intersection between the two tables.

INNER JOINs are the most frequently used JOINs. An INNER JOIN returns a result set that contains the common elements of the tables, i.e the intersection where they match on the joined column. Now we will create an INNER JOIN between these two tables. Run the queries below in your PostgreSQL console and follow along to view results in the users and addresses tables. Let's take a look at the data in the users table. In the data we set up, we have 3 users, but only 2 users have addresses. In the previous chapter, we designed the user and address table to have a one-to-one relationship. To demonstrate how INNER JOINs work, we will join the users and addresses tables. We have intentionally setup the data in the following ways: VALUES(3, 'My Third SQL book','Cary Flint', NOW()) INSERT INTO users (id, username) VALUES (3, 'Alice Munro') We do also add one extra book, but it isn't checked out by any users. The third user, Alice Munro, has no address, no books and no reviews. INSERT INTO reviews (id, book_id, user_id, review_content ) VALUES (2, '2 Elm Street', 'San Francisco', 'CA') INSERT INTO users (id, username) VALUES (2, 'Jane Smiley') She has an address at 2 Elm Street, San Francisco, CA, and has 1 book My Second SQL Book with a review by Jane Smiley. The second set of statements set up the data for the second user, Jane Smiley. Book: My Second SQL Book by John Mayer with a review Jane Smiley's review.Address: 2 Elm Street, San Francisco, CA.VALUES(2, 'My Second SQL book','John Mayer', NOW()) INSERT INTO reviews (id, book_id, user_id, review_content) INSERT INTO users_books (user_id, book_id, checkout_date) VALUES(1, 'My First SQL book', 'Mary Parker', NOW()) INSERT INTO books (id, title, author, published_date) VALUES (1, '1 Market Street', 'San Francisco', 'CA') INSERT INTO addresses (user_id, street, city, state)

INSERT INTO users (id, username) VALUES (1, 'John Smith') John Smith also has 2 reviews for each of the books. John Smith has an address at 1 Market Street, San Francisco, CA, and has checked out 2 books: My First SQL Book and My Second SQL Book.

The first set of statements set up the data for the first user, John Smith. Book: My Second SQL Book by John Mayer with a review My Second Review.Book: My First SQL Book by Mary Parker with a review My First Review.Address: 1 Market Street, San Francisco, CA.If you haven't run the statements, you can follow along and run them now as we explain the data and statements used to create them. Let's take a look at how these statements work. The table data can also be set up by running the following command in addition to the command at the top of the page. This mix of data lets us explore the various types of JOIN, and observe the results of queries. To set up the data, we will create 3 users, 3 books and 2 reviews in our library database by running the SQL commands shown below. To describe these different types, we need to add some data to our database so we can show the characteristics of each type. A table doesn't have to contain all the fields related to its rows, and table data can be used in multiple places. With JOINs, relational databases can reduce redundancy. JOINs are clauses in SQL statements that link two tables based on one or more fields. SQL handles relationships through the use of JOINs. Psql -d library < joins.sql What is a SQL Join? You can create those tables manually or restore the database from a backup file with the following command: The library database is needed to work through this chapter's content.įor this chapter, we will only need to start with the schema of the various tables we've created If you don't already have a database named 'library', then make sure to run createdb library from the terminal before starting this chapter.

Postgresql cross join