1 / 6

Database

Database. Week 5 SQL basics JOIN, ALIAS. JOIN. SQL joins are used to query data from two or more tables, based on a relationship between certain columns in these tables. JOIN(or INNER JOIN) : Return rows when there is at least one match in both tables

anika
Download Presentation

Database

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database Week 5 SQL basics JOIN, ALIAS Fox MIS Spring 2011

  2. JOIN • SQL joins are used to query data from two or more tables, based on a relationship between certain columns in these tables. • JOIN(or INNER JOIN): Return rows when there is at least one match in both tables • LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table • RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table • FULL JOIN: Return rows when there is a match in one of the tables

  3. INNER JOIN • SELECT column_name(s)FROM table_name1INNER JOIN table_name2ON table_name1.column_name=table_name2.column_name • SELECT Persons.LastName, Persons.FirstName, Orders.OrderNoFROM PersonsINNER JOIN OrdersON Persons.P_Id=Orders.P_IdORDER BY Persons.LastName • JOIN(or INNER JOIN): Return rows when there is at least one match in both tables

  4. Non-standard, but Common syntax • SELECT Persons.LastName, Persons.FirstName, Orders.OrderNoFROM PersonsINNER JOIN OrdersON Persons.P_Id=Orders.P_Id • SELECT Persons.LastName, Persons.FirstName, Orders.OrderNoFROM Persons, OrdersWHERE Persons.P_Id=Orders.P_Id • Above two will return the same results.

  5. Example • Database: Sakila, Table: Customer and Customer_list • Customer table manages customer’s first name and last name in separate fields, while customer_list table keeps them in one field. • Task: Make the list of customer id, customer name, their email addresses, and their active statuses. Sort them by customer id.

  6. ALIAS • SELECT column_name(s)FROM table_nameAS alias_name • SELECT column_name AS alias_nameFROM table_name • You can give a table or a column another name by using an alias • SELECT po.OrderID, p.LastName, p.FirstNameFROM Persons AS p, Product_Orders AS poWHERE p.LastName='Hansen' AND p.FirstName='Ola‘ • SELECT Product_Orders.OrderID, Persons.LastName, Persons.FirstNameFROM Persons, Product_OrdersWHERE Persons.LastName='Hansen' AND Persons.FirstName='Ola'

More Related