What are JOINs in SQL (for)?

Question

I have been using MySQL for 2 years now, but still do not know what you are actually doing with the statement JOIN. I did not come across a situation where I was not able to solve the problem with the instructions and syntax, which I already know ( SELECT, INSERT, UPDATE, ordering, ...)

• What does JOIN do in MySQL?
• (Where) Do I need her?
• Should I avoid this at all?

Answer 1

You probably used it without knowing it if you ever requested more than one table at a time.

If you run a query like

SELECT comment_text
FROM users, comments
WHERE users.user_id = 'sabwufer'
AND comments.user_id = users.user_id  <-- this line is your JOIN condition

Then you actually make the connection (INNER JOIN), even if you are not using the JOIN keyword.

( :

SELECT comment_text
FROM users
JOIN comments ON (comments.user_id = users.user_id)
WHERE users.user_id = 'sabwufer'

)

, , INNER JOINS, JOIN, , .

(., , XpiritO), .

Answer 2

SQL JOIN 2 .

SQL JOIN 2 ( ) , .

source ( ): http://www.sql-tutorial.net/SQL-JOIN.asp

Answer 3

JOINS, , .

JOINS ( XpiritO) . , ( ).

, , Customer :

firstName, lastName, customerId, address

"customerId" - . , :

category, name, price, productId

productId .

"":

customerId, productId

, . JOIN, , , . , , , , ..

:

SELECT * FROM Customer INNER JOIN Purchases on Customer.customerID=Purchases.customerID

Answer 4

- . JOIN, , , ,

. ,

play_id  play_title                 play_category
1        All Well That Ends Well  Comedy
2        As You Like It             Comedy
3        Antony and Cleopatra       Tragedy
4        Cymbeline                  Romance
5        Henry IV, Part 1           History
6        Henry IV, Part 2           History

JOIN. "", , . , "" "". :

play_category_id  play_category_name
1                 Comedy
2                 Tragedy
3                 History
4                 Romance

"" ,

play_id  play_title                 play_category
1        All Well That Ends Well  1
2        As You Like It             1
3        Antony and Cleopatra       2
4        Cymbeline                  4
5        Henry IV, Part 1           3
6        Henry IV, Part 2           3

, .

, , . ( , play_category_id.)

JOIN.

SELECT play_id, play_title, play_category_name
  FROM play
  LEFT OUTER JOIN play_category ON play_category = play_category_id

JOINING, , , .

, .

Answer 5

@XpiritO :

, :

Customer_ID
FirstName
LastName
...

.

Order_ID
Customer_ID
(description)

, . :

select top 1 tbl_customer.* from tbl_customers inner join tbl_orders on tbl_orders.customer_id = tbl_customers.id and tbl_orders.id={the order id you started with}.

,

customer_id first_name last_name order_id description

Answer 6

.

, .

:

SELECT * FROM authors LEFT JOIN books ON authors.id = books.author_id

SELECT * FROM authors, books WHERE authors.id = books.author_id

JOIN , , , . , , .

JOIN - , , , - , . ,

SELECT * FROM authors, books WHERE authors.id <> books.author_id

SELECT * FROM authors WHERE authors.id NOT IN (SELECT DISTINCT(books.author_id) FROM books)

, , JOIN, , , , , , .

Answer 7

As other people have said, INNER JOINs often appear when you need information that depends on information from another table. There are other valid points, for example, normalization. If you ask if there is anything you should do with the JOIN syntax, try making an OUTER JOIN.