Subqueries In Mysql Using subqueries in MySQL. Very helpful.

[cybernaut 0]

This method is very useful. I always use this when simple queries of linking two tables is not enough.This method is putting a query inside a query.Example:SELECT * FROM table1 LEFT JOIN (SELECT * FROM table2 WHERE field1='id') AS subtableor joining multiple queriesSELECT * FROM (SELECT * FROM table1 WHERE userid='001') AS subtable1 LEFT JOIN (SELECT * FROM table2 WHERE field1='id') AS subtable2You can join more than two queries depending on the output you want.If someone already posted this topic here I'm sorry I don't know.

[richard_site 0]

Starting with MySQL 4.1, all subquery forms and operations that the SQL standard requires are supported, as well as a few features that are MySQL-specific.

 

Subqueries in MySQL

http://forums.xisto.com/no_longer_exists/

 

MySQL 5.0 Reference Manual :: 12 SQL Statement Syntax :: 12.2 Data Manipulation Statements :: 12.2.8 Subquery Syntax

http://dev.mysql.com/doc/refman/5.7/en/subqueries.html

[iGuest 3]

Get another data from different serverSubqueries In MysqlHai to all the expert in MySQL.I'm a newbies in MySQL but at least I want to learn it a lot.I'm trying to get a data from another server while I'm still connecting to my local server. Is there any way I can get another data from another server while I'm still connected in my local server? But I need it in a single script? Is that possible? Do I have to use a subquery? If it is, could anyone give me some example of it. I haven't use any subquery while I'm using MySQL.If anyone could lightening my mind a bit I'm very grateful and appreciate it.Thanks before.-question by W4L4CH1

[ROdriguez 0]

Get another data from different server

 

Subqueries In Mysql

 

 

 

Hai to all the expert in MySQL.

I'm a newbies in MySQL but at least I want to learn it a lot.

I'm trying to get a data from another server while I'm still connecting to my local server. Is there any way I can get another data from another server while I'm still connected in my local server? But I need it in a single script? Is that possible? Do I have to use a subquery? If it is, could anyone give me some example of it. I haven't use any subquery while I'm using MySQL.

If anyone could lightening my mind a bit I'm very grateful and appreciate it.

Thanks before.

 

-question by W4L4CH1

You can connect to multiple databases by creating multiple database handlers. I think PDO can do that. And subqueries have nothing to do with your question.

[wutske 0]

This method is very useful. I always use this when simple queries of linking two tables is not enough.
This method is putting a query inside a query.

Example:

SELECT * FROM table1 LEFT JOIN (SELECT * FROM table2 WHERE field1='id') AS subtable

or joining multiple queries

SELECT * FROM (SELECT * FROM table1 WHERE userid='001') AS subtable1 LEFT JOIN (SELECT * FROM table2 WHERE field1='id') AS subtable2

You can join more than two queries depending on the output you want.

If someone already posted this topic here I'm sorry I don't know.

SELECT * FROM table1 LEFT JOIN tables2 WHERE table2.field1='id'

What's wrong with that ?

Somebody once told me that using subqueries is not the best way to work with SQL databases.

[Quatrux 4]

Yes, you're right, sometimes it's better to not use a subquery if there is a more simple way, but sometimes a subquery can be very useful, especially if you're using a lot of tables and want to join different data and you have some kind of expressions you need to choose and create with a case and later use it in a WHERE clause.

Sometimes, due to indexes like primary keys a subquery may work 10 times faster depending on data structure, for example:

This works 10 times faster depending on how you join and what are the primary keys.

...AND t2.code IN (select t1.code from username.table1 t1 where t1.other_code = 'somekind of a code')

than

...AND t2.code IN ('somekind of a code')

due to it needs to go through much less rows, but it may always differ, so you need to test and understand the data structure.

Also if the database has thousands of rows, subquerys may be quite slow, especially using it in: IN (select .. from..) or using it in table columns like this: select name, (select .. from) as status, address from table1;

But if the database is a large one, subquerys are important, as if you need to count something, or use case and later you need to some kind of expression from that data, or after using a UNION and etc.

It's good to have somekind of a SQL tool to test your queries on a MySQL or an Oracle server. For Oracle I know a very good tool is PLSQL Developer.
Edited May 13, 2010 by Quatrux (see edit history)

[yordan 10]

That's why, with Oracle, people usually ask for the execution plan of a given request, in order to understand why it runs faster than another, similar but different, sql request. Sometimes the actual execution plan strongly differs from what you would logically expect.

[vhortex 1]

Subquery on my opinion is not usefull most of the time, there are people that i know that bloats there queries with subqueries that renders them unreadable. It is still dependent on how the keys are assigned from the tables they come from and the indexes.

Sometimes building a temporary table base on a query then using another query on it to fetch the values tends to be much faster than the subquery versions.. there are also limitations on this method as such memory and resources are being used.

It must also be noted that a subquery seems to be restricted to return 1 value of data unless this was changed on recent databases. The last time I used a subquery was 4 years ago. This is exception if the subquery is used inside "IN" clause but this can slow down the system than speed it up.

I best use I found with subquery is if it was used like this

SELECT *, (SELECT COUNT(*) FROM `softusers`) AS `sofwareusers` FROM `softwares` WHERE `softwarename` = 'MBAM';

Subquery is just an extra addon, using it is a trick but there are times where getting pcs of codes is faster when using chain selects and subquery.

[Quatrux 4]

To add more, I remember usually it was enough on MySQL to use one SELECT or with at least one subquery and usually with several joins, but when I started working with Oracle.. it's another story..When you're database is large and has a lot of different information, lots of tables, and especially when working with Oracle reports, I need to write a lot of subqueries to format my data, a lot of union all/minus and and etc.At first it was strange for me, but I got used to it, usually when using it in web, I create a view and use it as a table, of course it depends how fast it is, sometimes the only way is to create a materialized view or to put all data in a seperate table and create a job for it to update several times in a day.. Of course, there are plenty of Oracle reports which runs ~9 seconds or even much more, depends on the parameters. :)I usually need to use subqueries when using group functions to count something, to sum up something and later use those numbers in another place.. in the same query

Edited August 26, 2010 by Quatrux (see edit history)

[vhortex 1]

What I learn from Oracle was the virtue of laziness and the use of imagination. I came up with this query when playing with Oracle and applied it to MySQL and both works perfectly

SELECT SUM(`resultpoints`) AS `sumpoints`, COUNT(`resultpoints`) AS`countpoints`, (SUM(`resultpoints`)/COUNT(`resultpoints`)) AS`average`, `studentid` FROM `results` WHERE `countpoints` > 0  GROUPBY `studentid` ORDER BY ` average` DESC

The target table where this query was used is a list of unkown number of scores of all fighters, the number of entries was more than 3 million and I need to fetch the top 15 fighters base on the total and average score.

It was a headache but experiments on the use âviewâ tends to get way too long to process (MySQL and Oracle) and started to consume large memory (MySQL). Using the subquery approach yields negative result since the query doubles on load time and triple on memory and processor usage.

The query posted run for 0.005 seconds on my test machine and 0.0001 on our live busy server (fetching top 300).

***************

The thing I learned with Oracle and views is the fact that each view generates temporary tables and while they are working much better on Oracle, they will choke MySQL if the view query was not optimized.