An SQL join clause combines records from two or more tables or views.
2
votes
2answers
242 views
get column from too many tables in mysql
.I have too many tables X1,X2,...Xn (they might exceed 75 tables) in Mysql DB to represent departments of a very large company.
every table has structure something like this
desc X1;
...
5
votes
6answers
6k views
Is it possible to mysqldump a subset of a database required to reproduce a query?
Background
I would like to provide the subset of my database required to reproduce a select query. My goal is to make my computational workflow reproducible (as in reproducible research).
Question
...
20
votes
1answer
941 views
USING construct in JOIN clause can introduce optimization barriers in certain cases?
It was brought to my attention that the USING construct (instead of ON) in the FROM clause of SELECT queries might introduce optimization barriers in certain cases.
I mean this key word:
SELECT *
...
6
votes
2answers
1k views
SQL Server Join/where processing order
After reading Slow SQL query, not sure how to optimize, it got me thinking about the general performance of queries. Surely, we need the results of the first table (when other tables are joined) to be ...
6
votes
1answer
173 views
Are relations slower than a big, inefficient table?
I've been asked in my job to violate the first normal form (repeating groups across columns, using empty / null values) several times, "for the sake of computer processing power". In a nutshell, a ...
9
votes
3answers
178 views
Are there any database engines which will intuit the join condition based on the existing foreign keys?
It seems strange to me that, when I've defined a foreign key, the engine cannot use this information to automatically figure out the correct JOIN expressions, but instead requires me to re-type the ...
6
votes
2answers
2k views
What is more efficient, a where clause or a join with million plus row tables?
We run a website that has 250MM rows in one table and in another table that we join it to for most queries has just under 15MM rows.
Sample structures:
MasterTable (Id, UserId, Created, Updated...) ...
6
votes
1answer
178 views
Table order in Join Query
if i say:
Table1 left join Table2
is it the same as saying
Table2 right join Table1
?
In other words, should i expect to get the same results from 2 identical queries where the only thing that is ...
3
votes
3answers
309 views
SQL JOIN Syntax in MS SQL
I have been taught in my MSSQL classes, this is how to join two tables
select * from FirstTable A
JOIN SecondTable B
on A.ID= B.ID
Now in my professional life, I came across JOIN queries like ...
2
votes
1answer
119 views
Update ranking on table
I have two tables:
mysql> desc rank ;
+----------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
...
2
votes
2answers
275 views
Select rows with repeat data within certain timeframe
As a follow-up question to my previous question on difficult row categorisation, I have another problem which I'm sure will be easily solvable. After this I plan on reading a good book or two on ...
1
vote
1answer
30 views
How to add additional tables/fields into a query with multiple inner joins?
Given this query (many thanks to RolandoMySQLDBA from question Select records that do not have associations outside a certain list), how do I add other fields from other tables into my result?
SELECT ...
0
votes
0answers
111 views
Which update is faster using join or sequential?
My this question is in sequence of my previous question I asked.
I could write two solutions using Stored Procedure instead of trigger or nested-query .
Both use a helper function ...
0
votes
3answers
457 views
INNER JOIN making COUNT(*) slow
I have a very simple query:
SELECT COUNT(*)
FROM messages
INNER JOIN users ON messages.user_id = users.user_id
With the join it takes 1146 ms and without it takes 220 ms (220 ms still seems slow to ...
