An SQL join clause combines records from two or more tables or views.

learn more… | top users | synonyms

3
votes
1answer
176 views

postgres filtering select based on combination of joins

I am having trouble filtering a request that depends on a combination of child elements (one-to-many relationship) Please consider the following example. CREATE TABLE sample_container ( id serial ...
12
votes
1answer
344 views

Why does subquery use parallelism and join doesn't?

Why does SQL server use parallelism when running this query which uses a subquery but it doesn't when using a join? The join version runs in serial and takes around 30 times longer to complete. Join ...
3
votes
2answers
545 views

cross database way to force join strategies

[how to enforce the query to use certain join algorithm] Knowing that there are several joining algorithms [Nested Loop; Sort Merge; Hash Join], when implementing a query , is there any technique to ...
0
votes
1answer
99 views

Would it be better to do a sepparate query instead of a left JOIN?

Only a couple of rows is returned when not using LEFT since there isn't always data in the associated table - recipe_preparation. This is one of a couple of tables with the same "issue". SELECT r.id, ...
1
vote
2answers
221 views

MYSQL joining 3 tables with left join

I have these tables: pupils: (id_pupil, name, surname, email, user, pass, level, class) incidents: (id_incidents, date, time, type_incident, comments, id_pupil, id_user, subject, id_trimester) ...
5
votes
1answer
3k views

Can I provide a default for a left outer join?

Suppose I have tables a (with column a1) and b (with columns b1 and b2) and I perform a left outer join SELECT * FROM a LEFT OUTER JOIN b ON a.a1 = b.b1 Then b1 and b2 will be NULL where a value of ...
0
votes
0answers
342 views

Alternative to TEMPORARY tables capable of re-opening in a query?

I use TEMPORARY table for calculation of temporary data, but TEMPORARY tables in mysql has a big limitation, which made it useless for me. A table cannot be re-opened in a query. This mean I cannot ...
0
votes
2answers
76 views

Is my JOIN syntax not valid? And have I understood JOIN correctly?

I've been reading up a bit regarding JOINs. And i just noticed that I've been writing the syntax a little bit different. Although I don't think there's any problem with it, I would like to hear your ...
0
votes
1answer
394 views

Does MySQL load the entire row into memory, or just the columns used in WHERE, ORDER BY and GROUP BY?

I have a table that isn't very large row wise (in the 100ks range), but that contains a lot of raw data that is very large in size. Despite having a relatively little number of rows, it's around ...
1
vote
1answer
514 views

How to perform a multiplication by a different select query

Hope my question is accepted here and I haven't made a mess out of renaming the tables and columns so you can understand what I need! :) Query A: DECLARE @var VARCHAR(7) SET @var = 'xtpo' SELECT ...
2
votes
1answer
3k views

UPDATE a column by COUNT of another table

It might be a naive question, but what is the difference of these two queries and which is preferred? UPDATE table1, (SELECT id,COUNT(*) idcount FROM table2 GROUP BY id) AS B SET table1.Freq = ...
0
votes
1answer
98 views

Get all items from list table which don't have a successful log entry for 'sent' in log table

How might one retrieve items that when joined either don't have a specific field (sent), or if they do have that field (sent) then they have an additional field (failure_reason) as well for every sent ...
2
votes
1answer
131 views

Clustered Index / Index Oriented Table performance in join, worse than nonclustered?

Can a clustered index (or IOT in Oracle) be detrimental, when to be used on a very "broad" table, but only few columns are used? In this case, the "Product" table is used only like a junction table ...
2
votes
2answers
4k views

Full outer join does not work for two small tables, error 1064

folks. I am currently exploring the capabilities of MySQL while doing simple exercises with diverse queries. I was reading the following article that explains how table JOIN works: ...
1
vote
1answer
1k views

Using OFFSET in a large table by a simple sub-query

The slow performance for using OFFSET in large table has been widely discussed in various blogs, and the most efficient way is to use an INNER JOIN as SELECT * FROM table INNER JOIN ( SELECT id FROM ...
1
vote
1answer
60 views

Formulating a Join Query for PostqreSQL

I have a quick question, if someone can help, I would be grateful I have the following tables User, Video, User_video user has userdetails (user_id, name, email) video has videoDetails ...
1
vote
0answers
128 views

join performance, index vs. index+foreignkey

I'm using an orm framework that handles data (and its integrity) on top of mysql. So far I ignored foreign keys because the orm handles that just fine. I recently read that foreign keys can enhance ...
1
vote
1answer
7k views

Select records in one table but not in another, when the second table is not matching on primary key

I have two tables from which I need only the results that appear in one table (list) which do not appear in the second table (cardinal). The list table's primary key is sku, and the table has a ...
1
vote
0answers
150 views

Relation between lossy decomposition and Natural join?

I am confused with the lossy decomposition and natural join. Natural join(with common attribute): only respective common attribute for the resultant table is formed. Lossy: Natural join on tables ...
0
votes
2answers
2k views

How can the LEFT JOIN return more results than the unjoined query?

Consider the following queries: mysql> SELECT count(*) FROM list l WHERE l.source='blink'; +----------+ | count(*) | +----------+ | 3372 | +----------+ 1 row in set (0.00 sec) mysql> ...
1
vote
0answers
147 views

MySQL join query indexes

After recently discovering the MySQL feature to log queries that aren't using indexes, I've been chasing these down in a large app, looking for places to improve performance. There is at least one ...
1
vote
1answer
2k views

Calling another function inside a postgres function

I have a function A and function B. Function A Name is f_a() and the code is: Select id,address from A; Function B Name is f_b() code is: Select C.address,B.name From (Select id,name from B, ...
1
vote
1answer
95 views

Estimating a Size of Joining a Relation with itself

I'm studying size estimation of logical query plans in order to select a physical query plan. I was wondering what is the size of joining (natural join) a relation to itself? e.g R(a,b) JOIN R(a,b)
1
vote
2answers
127 views

Duplicates while doing multiple joins

I have 4 MySQL tables 1. companies - {id*, company} 2. employees - {id*, fname, lname, id_company*} 3. technologies - {id*, technology} 4. company_technologies - {id_technology*, id_company*} Field ...
0
votes
1answer
213 views

Mysql list data from left join query

I have 2 table and i want to pull data from both of them. This is sample for my table structure: Table tbl_a = id, time, note, customer Table tbl_b = id, time, name, surname, note This is my mysql ...
0
votes
1answer
245 views

Data Analysis with MongoDB or MySQL

I have a need for processing some data in order to come up with some statistic. This is the scenario: Each user can answer several questions from the question list. They pick their answers from a ...
0
votes
1answer
166 views

Join and Count based on Specific User ID in SQL

I have the following tables, I inserted some dummy data in it for demonstration purposes. I would like to join these two tables in a way that based on the user ID, I can get the count of similar ...
1
vote
1answer
311 views

INSERT INTO selected columns creates new column from JOIN [closed]

I have an INSERT INTO ... SELECT.. query that uses a JOIN on two tables. The results of the query should take a subset of the information and insert it into an existing table. That part works, but the ...
0
votes
1answer
654 views

Is it better to put filters on JOIN clauses instead of WHERE clauses

MySQL 5.5.8: Question 1: Is there any difference in the following statements and if so, which is preferred performance wise? Can this even be answered without knowing the row counts of the tables ...
0
votes
1answer
133 views

Merge two tables on partly matching column

I have below 2 tables. old_name old_value Tom1 100 Kate1 80 Jim1 70 new_name new_value Tom2 70 Kate2 100 Jim2 80 I want to make a new table look like ...
0
votes
1answer
325 views

Update , Select and Join using 3 tables

There are 3 tables: Label (Label_ID,Label_Name), Label_ID is Primary Key. Tracks (Prefix, Label_ID), Prefix is Primary Key,Label_ID is empty column Top100 (Prefix,Label) No keys in this table. My ...
4
votes
2answers
5k views

SQL join query to show rows with non-existent rows in one table

I am trying to get some reporting done for employee time records. We have two tables specifically for this question. Employees are listed in the Members table and each day they enter time entries of ...
0
votes
1answer
37 views

Sorting by a field in child table

There are two tables: contract (with primary key contractid) and contractupdate (with field contractid referring to a contract). contractupdate also has DATE field startdate and TINYINT field day. I ...
0
votes
2answers
211 views

Which is better? A join or a SELECT IN? [closed]

I have a set of ids that I extract using a join of 2 tables. Now I need some information from 2 other tables. What would normally be the best approach? So a SELECT IN using the set of ids I already ...
0
votes
1answer
214 views

Optimizing multiple MySQL sub-queries and joins for multiple runs

The query (below) that's running is taking ~0.6 sec which normally would be fine but it gets run almost 1300 times for different custids. How can I make it run faster? Setup: Mac OS X 10.7.5 MySQL ...
1
vote
1answer
27 views

Optimised query to select document and possible annotations

I have a database of small documents which users can view and annotate. Their annotations are not public. I'm looking to optimise a single query to read the document and annotations for the current ...
5
votes
1answer
12k views

Syntax of INNER JOIN nested inside OUTER JOIN vs. query results

TLDR; If you look at the 2 execution plans, is there an easy answer to which is better? I purposefully did NOT create indexes so it's easier to see what's happening. Following up on my previous ...
0
votes
1answer
42 views

How to create new column with value from other table

I have a table called 'menus': menu_id | sort | page_id | parent_page_id ---------|------|---------|---------------- 1 | 0 | 1 | NULL 1 | 1 | 2 | 1 1 | 2 | ...
3
votes
2answers
2k views

Does using LIMIT improve the performance and is it noticeable?

I want to understand the following. Assume that I have a complicated query with let's say a join of 5 tables a group by summations and order by. Letting aside any optimizations to the query itself ...
1
vote
1answer
355 views

Slow join behaviour with 'or' in predicate

I'm faced with a situation which I can't understand and overcome. In short we have left-join query like: select from a left join b on a.key1=b.key1 or a.key1=b.key2 This works VERY slowly, while ...
1
vote
1answer
166 views

How to prevent Cartesian joins when value can be in either field?

I have a table of articles where the same article may be presented to different audiences with slightly altered text depending on the user's access level. When this occurs, the article will have the ...
0
votes
0answers
830 views

MySql INNER JOIN doesn't use composite index

I have two tables, table A which contains date and name of table B entry and table B which contains its name and type. Table a has a composite index on table_b_name and date. I'm selecting all table A ...
0
votes
1answer
143 views

Need to create combinations of Parents/Children/Dogs, but can't get past 2nd level of CTE

I've hit a brick wall and please ask help. I'm building a table up for (in this example) a ticketing system where e.g. Ticket 1 will allow Parent: Either parent Peter or Sally, Child: to come without ...
2
votes
3answers
3k views

Conditional Join - Specific Column Select

I have tables like these: Table Employee ( Emp_Id, Full_Name, ...) Table Order ( Submitter_Id, Recipient_Id, ...) Then say that I have conditional join like this: select isnull(Emp.Full_Name, ...
-1
votes
1answer
73 views

COUNT WITH JOIN [closed]

I've the following tables: fss_post(post_id,text) fss_comment(idComment, post_id, text) Is possible to write a query that give me as result a row for each post_id with total comment count for ...
0
votes
1answer
227 views

Get unmatched rows for foreign key

Consider two tables: CREATE TABLE Users( id INT PRIMARY KEY, name VARCHAR(128) ) Engine=InnoDB CREATE TABLE Tags( id INT PRIMARY KEY, user REFERENCES Users(id), tag VARCHAR(128) ) ...
0
votes
1answer
89 views

Performance: order of tables in joined statement

I have the following SQL statement, running on a SQLite database on a Windows mobile device. SELECT table1.uniqueidentifier1, table1.int1, table1.varchar1, table1.decimal1, table1.decimal2 ...
0
votes
1answer
51 views

Append columns with values from has many reletionship

I have two tables orders and items. Every Order has many Items. orders - id - created_at - paid_at items - id - title - amount - quantity - order_id Let say that there are 3 kinds of items. Chair, ...
0
votes
1answer
1k views

Query with PIVOT fails with second LEFT OUTER JOIN to same table

I have a big query that contains a PIVOT and several LEFT OUTER JOINS. I need to add a second LEFT OUTER JOIN to a table that is already joined in the query. When I do this the query errors and I'm ...
2
votes
1answer
178 views

Joining on a field with different values

I am trying to join data from two completely different sources. One source contains an employee's schedule information, and the other tracks what they actually worked (like what time they actually ...