CODEWITHSUNDEEP
mysqlsql
Jess McKenzie
Jess McKenzie

Reputation: 8385

Joining two tables unsure of join to use

I have to get data from two SQL tables but I am unsure of what join to use and the same tables have the same customers_id table would I use an inner join?

table - customers

customers_id
customers_firstname
customers_lastname
customers_email_address

table - orders


customers_id
customers_street_address
customers_suburb
customers_city
customers_postcode
customers_state
customers_country

Upvotes: 2

Views: 77

Answers (4)

Suresh
Suresh

Reputation: 1504

INNER JOIN Ya you can use it.

You can write below query to get your all data from two tables.I am going to write full join query for you.y

select customers.customers_firstname,
       customers.customer_lastname,
       orders.customers_city 
from customers 
INNER JOIN orders 
  ON customer.customers_id = orders.customers_id

Upvotes: 0

onedaywhen
onedaywhen

Reputation: 57023

the same tables have the same customers_id table

I think you meant to say they have a common column, being customers_id, in which case you can use NATURAL JOIN.

Upvotes: 0

Abe Miessler
Abe Miessler

Reputation: 85046

It depends on what results you want. If you want every record in Customers regardless of whether there is a matching record in orders the you would use an LEFT OUTER JOIN. If not you would use an INNER JOIN. This is the article I typically refer people to for a basic explanation of joins.

An INNER JOIN would look like this:

SELECT c.customers_id, 
       c.customers_firstname, 
       c.customers_lastname, 
       o.customers_street_address,
       o.customers_city,
       o.customers_postcode,
       o.customers_state
FROM customers as c
INNER JOIN orders as o
ON c.customers_id = o.customers_id

I purposely did not do select *. Try to get into the habit of only selecting the columns you want from tables instead of everything.

Upvotes: 1

user830639
user830639

Reputation:

The join you should use depends on which information you are trying to retain, based on the customer ID. If you're looking to retain all of the information from customers even if there is no match with orders you would use a left join. Conversely, if you wanted to retain all the information from orders even if there were no match with customers you would use a right join.

If you want to return all of the information, and combine rows which have a match, use a full join. Finally, you can use an inner join if you only want the rows which have a match from either table.

Upvotes: 0

Related Questions