In MySQL can you use JOIN for INSERT queries?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

In MySQL can you use JOIN for INSERT queries?

Post by JAB Creations »

In MySQL can you use JOIN for INSERT queries? This is simply a yes or no question; if yes then I'll post my attempt a little later after I get a reply. :)
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: In MySQL can you use JOIN for INSERT queries?

Post by jaoudestudios »

I dont think so. But someone correct me if I am wrong!

What engine are you using?

If you are thinking of performance and using the InnoDB engine then transactions inserts will be much faster.
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Re: In MySQL can you use JOIN for INSERT queries?

Post by JAB Creations »

I'm not sure what engine it is...though all the table "types" in phpMyAdmin are reported as MyISAM. I was not aware that there were numerous "engines" for MySQL?

Would using JOIN reduce or increase server load overall? My head is getting dizzy with all the things I'll need to update while I write out even very modest features in to the forum software.
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: In MySQL can you use JOIN for INSERT queries?

Post by jaoudestudios »

There are many types of Mysql engines. MyISAM is the default, but not necessarily the best! It really depends on what you are trying to acheive.

JOINS do increase server load, but there is no simple way around it, it is required for normalised databases - that is why some developers un-normalise part of their database that is going to be search alot. Doing multiple queries in php is worse, because you will send 1 query to the MySql server then back to php and then back to the MySql server again.

I use InnoDB engine because it has row locking rather than MyIsam table locking, plus lots more (transactions - which are kind of like undos). On the other hand MyIsam has full text search.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: In MySQL can you use JOIN for INSERT queries?

Post by Eran »

A join can be performed for a select statement only, I can't imagine what you'd want to use it in an insert.

Personally, I use Innodb as the storage engine for all my tables. Transactions, row-level locking and other factors make it preferable to MyISAM for me.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: In MySQL can you use JOIN for INSERT queries?

Post by Benjamin »

You can perform joins on select, update and delete queries at a bare minimum.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: In MySQL can you use JOIN for INSERT queries?

Post by Eran »

Maybe in prepared statements but not in regular queries... as far as I know. Can you give an example of an update / delete statement with a join that works on MySQL?
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: In MySQL can you use JOIN for INSERT queries?

Post by Benjamin »

Here is an example update query joining tables

Code: Select all

 
UPDATE
  admin_users au,
  event_log el
SET
  au.userid = 1,
  el.userid = 1
WHERE
  au.userid = 1
  AND au.userid = el.userid
 
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: In MySQL can you use JOIN for INSERT queries?

Post by Eran »

Interesting :)
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: In MySQL can you use JOIN for INSERT queries?

Post by Benjamin »

I think the MySQL manual is so hard to understand for the average developer that there are quite a few things that are very obscure and unknown.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: In MySQL can you use JOIN for INSERT queries?

Post by Eran »

I actually found the manual to be quite informative for the most part. For some reason I thought the join would appear in the syntax part, and it does but not in an obvious way:
FROM table_references ...
Where table_references can be multiple tables. I haven't used this format in a join in a long time.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: In MySQL can you use JOIN for INSERT queries?

Post by Benjamin »

Well the way I understand it, you're specifying what (fields) & tables to update after the UPDATE part of the query and specifying how to join them in the WHERE clause. I can't remember offhand the exact syntax for deletes, it might be a little bit counter intuitive but it is possible as I have done it before.
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Re: In MySQL can you use JOIN for INSERT queries?

Post by JAB Creations »

The reason I wanted to know if JOIN's could be used in INSERT queries was to reduce the number of INSERT queries overall. If you can SELECT from multiple tables using JOIN why the heck wouldn't you be able to write to multiple tables?

Then again I don't know about the performance difference of (if you could) JOIN in an INSERT versus multiple INSERT queries.

At what point did everyone learn about MySQL engines? At what general point did any change in performance begin to justify changing from the default engine?
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: In MySQL can you use JOIN for INSERT queries?

Post by jaoudestudios »

Its not just about performance, each has different pros & cons.

If you are thinking of performance on INSERTs then use InnoDB with transactions or use MySQLi with multiplie queries.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: In MySQL can you use JOIN for INSERT queries?

Post by VladSun »

It looks like you need logging, right?
If so, you may find using triggers useful.

Just to clarify the "INSERT-JOIN" question - imagine everything in RDB as a table. Tables which have a physical instance (i.e. a created TABLE) are "writable", all of the others (SELECT results, VIEWs, JOINs, etc. ) are "read-only".
By performing a JOIN you get a new, "virtual" table (i.e. "read-only" one), so you can't INSERT into a "JOIN-table".
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply